Convert Numbers into Actual Time

[Chris Waller]

I have a Spreadsheet completed by a colleague who does not understand anything about Excel and I am having to try to sort out a problem caused by a user.

What has happened on several columns which have been formatted as numbers with two decimal places actually represent times for example 10.45 equates to 10 minutes 45 seconds, 0.50 represents fifty seconds, 1.10.50 represents 1 hour 10 minutes and 50 seconds. As there are over 1,200 occurances in 6 seperate columns, does anyone know of a way to easily change these to actual hours, minutes and seconds that they represent without making an absolute hash of the Spreadsheet? TIA</SPAN>

 

[Weazel]

maybe if you can add a column and try

=1*SUBSTITUTE(A1,".",":") formatted as time ?

 

[shyy]

Was going to suggest the same thing, Weazer why did you put the 1* infront of the substitute? I would have just left it =SUBSTITUTE(A1,".",":").

 

[Weazel]

just to convert it back to a number. after substituting, the cell would a number formatted as text so if you needed to do some math it would be an issue. I usually multiply by 1 to get it back to a number but you could also just +0 at the end, then format it as time.

If you didn't need it as a number you could leave it out and just keep it as text I suppose.

 

[Chris Waller]

Thanks guys for your input, but it didn't actually work. Sorry about that!

 

[Andrew Poulsom]

In what way didn't it work?

 

[Jonmo1]

maybe if you can add a column and try

=1*SUBSTITUTE(A1,".",":") formatted as time ?

I think this will have a problem with 10.45
Converts to 10:45 which excel will consider 10 Hours and 45 Minutes, not 10 Minutes and 45 Seconds.


Try
=TEXT(SUBSTITUTE(A1,".",""),"00\:00\:00")+0

 

[shyy]

Thanks for the explanation

just to convert it back to a number. after substituting, the cell would a number formatted as text so if you needed to do some math it would be an issue. I usually multiply by 1 to get it back to a number but you could also just +0 at the end, then format it as time.

If you didn't need it as a number you could leave it out and just keep it as text I suppose.

 

[Chris Waller]

When formatted the 50 seconds has changed to 5 minutes and the 10 minutes 45 seconds has changed to 10 hours 45 minutes. Perhaps its a case of all those figures that already look like hours need to be treated one way. The ones that look like minutes and seconds another way and the ones that only represent seconds have to be treated a third way. What do you think?

 

[Rick Rothstein]

When formatted the 50 seconds has changed to 5 minutes and the 10 minutes 45 seconds has changed to 10 hours 45 minutes. Perhaps its a case of all those figures that already look like hours need to be treated one way. The ones that look like minutes and seconds another way and the ones that only represent seconds have to be treated a third way. What do you think?

See if this formula works for you...

=IF(COUNTIF(A2,"*.*.*"),1*SUBSTITUTE(A2,".",":"),IF(A2="","",1*SUBSTITUTE("0:"&TEXT(A2,"0.00"),".",":")))

Note: You will have to use Cell Format to change the display from a time serial number to the time display format of your choice.