# Solved User entered 23:59 as 23.59, need to convert back to 23:29

Microsoft Excel 2010 - complete product...
July 7, 2013 at 04:06:55
Specs: Windows 7
 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

See More: User entered 23:59 as 23.59, need to convert back to 23:29

#1
July 7, 2013 at 06:59:07
 Try this for a complete solution:=(LEFT(A1,2)&":"&RIGHT(A1,2))*24Drop 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.

Report •

#2
July 7, 2013 at 08:27:58
 For a conversion that also shows the correct time, before changing to decimaltry this:=TIME(LEFT(A1,2),RIGHT(A1,2),)for the decimal conversion=TIME(LEFT(A1,2),RIGHT(A1,2),)*24You 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,":")*24Then 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)))*24So many way to do it.MIKE

Report •

#3
July 7, 2013 at 09:49:09
 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 TabSelect Find & SelectSelect ReplaceIn the Find What box enter a period or dotIn the Replace With box enter a colonSelect Replace All and your done.Just make sure all the cells are formatted as Time.You can now do your decimal conversion.MIKEhttp://www.skeptic.com/

Report •

Related Solutions