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