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 •

#1
July 7, 2013 at 06:59:07
✔ Best Answer
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.


Report •

#2
July 7, 2013 at 08:27:58
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

http://www.skeptic.com/


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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question