Excel User entered 23:59 as 23.59, need to convert back to 23:59, so that I can convert back to correct decimal eg.. Correct Result for 23:59 is 23.98

23:59*24 = 23.98

Try this for a complete solution: =(LEFT(A1,2)&":"&RIGHT(A1,2))*24

Drop the *24 if just want to see the "time", although it will actually be a text representation of the time. That may or may not work as an actual time depending on what you need to do with it. Excel is funny like that.

A better solution would be to use Data Validation to ensure that the users enter an actual time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

For a conversion that also shows the correct time, before changing to decimal

try this:=TIME(LEFT(A1,2),RIGHT(A1,2),)

for the decimal conversion

=TIME(LEFT(A1,2),RIGHT(A1,2),)*24

You could also use the REPLACE() function, but it also unfortunately gives the answer as a TEXT string that looks like Time.

=REPLACE(A1,3,1,":")

for the decimal conversion

=REPLACE(A1,3,1,":")*24

Then there is the TIMEVALUE() function that we can use with DerbyDad03 solution and it will also give you the time before the decimal conversion:

=TIMEVALUE((LEFT(A1,2)&":"&RIGHT(A1,2)))

for decimal

=TIMEVALUE((LEFT(A1,2)&":"&RIGHT(A1,2)))*24

So many way to do it.MIKE

Forgot to mention, that if you have a large number of Times entered incorrectly,

probably, the simplest way to convert them, is to use Find & Replace.Select all the cells with the times entered incorrectly,

On the Ribbon select Home Tab

Select Find & Select

Select Replace

In the Find What box enter a period or dot

In the Replace With box enter a colon

Select Replace All and your done.Just make sure all the cells are formatted as Time.

You can now do your decimal conversion.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History