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

Report •

July 7, 2013 at 06:59:07
✔ Best Answer
Try this for a complete solution:


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.

Report •

July 7, 2013 at 08:27:58
For a conversion that also shows the correct time, before changing to decimal
try this:


for the decimal conversion


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


for the decimal conversion


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:


for decimal


So many way to do it.


Report •

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 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.


Report •

Related Solutions

Ask Question