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>

 

[Chris Waller]

Thanks that worked for a couple of my examples however, the 50 seconds now looks like 5 seconds.

 

[Jonmo1]

This works for me

Excel Workbook
	A	B
1	10.45	0:10:45
2	1.10.50	1:10:50
3	50	0:00:50
Sheet1

 

[Rick Rothstein]

Thanks that worked for a couple of my examples however, the 50 seconds now looks like 5 seconds.

Are you referring to my formula? If so, are your values text or numbers? I assumed they were text because you showed 0.50... numbers cannot retain a trailing zero but text representations of numbers can. My formula will not work if those values are numbers.

 

[Jonmo1]

OK, I see now..

0.50 is being entered for 50 seconds.
Excel converts that to 0.5 (removes the trailing 0)

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

 

[Andrew Poulsom]

Are you referring to my formula? If so, are your values text or numbers? I assumed they were text because you showed 0.50... numbers cannot retain a trailing zero but text representations of numbers can. My formula will not work if those values are numbers.

The number format 0.00 displays 0.5 as 0.50.

 

[Chris Waller]

Rick, That worked a treat Kudos to you</SPAN></SPAN>. Brilliant! I have been trying to sort this out since the middle of last week. I will keep this formula in my Workbook. No doubt it will come in very handy especially if I have to help out on the same task that I am currently doing.

I will give this a whirl at work tomorrow where I have a thousand or so more entries to try this out on. Thanks once again. I can perhaps sleep a little easier tonight. Thanks again.

 

[odsmith]

Just a quick question. Why could he just do a find and replace "." ":" ?

 

[Rick Rothstein]

The number format 0.00 displays 0.5 as 0.50.

Yeah, but actually what I wrote did not apply (it was a knee-jerk reaction based on a different formula that I did not end up posting). The TEXT(A2,"0.00") part of my formula handle the number problem.

 

[Jonmo1]

Just a quick question. Why could he just do a find and replace "." ":" ?

See post #7

 

[Weazel]

Thanks Jonmo and Rick, I should have paid closer attention.

I'm wondering if you can explain what the "\" is actually doing in the text format?

I've been meaning to ask for a while because I don't quite understand it.