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>

 

[Rick Rothstein]

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

It makes the TEXT function see the character that follows it as a character without an special meaning. For example, if you had the number 1234 in A1 and you wanted to display it with a colon in the middle ("12:34"), you cannot just do this...

=TEXT(A1,"00:00")

because the colon used like that makes the TEXT function think A1 contains a time value, but 1234 is not a valid time value, so the function will display a #VALUE! error. However, if you put a back slash in front of the colon, it will lose its special meaning as a time delimiter and just become a simple character. Hence, this works...

=TEXT(A1,"00\:00")

The other way you can do this is by surrounding the colon with double quote mark (quote marks internal to a text constant must be doubled up), it will be seen as just a text character, and so will output the same value...

=TEXT(A1,"00"":""00")

 

[Weazel]

Thanks for breaking it down for me Rick I really appreciate it.

Your explanation really cleared up my confusion, thanks again

 

[xsil]

Don't know I'm late to this party but.. Did you try the following? (Especially if your colleague is not a "formula person" the following approach might be easier to explain, so next time they can fix it themselves!):

1. Select the values in question (ie the original times using ".")
2. Click Data > Text-to-Columns to launch the Text To Columns Wizard
3. (Wizard Step 1 of 3) Choose the 'Delimited' option, click Next
4. (Wizard Step 2 of 3) Check the 'Other' option on the left hand side, enter a . in the box next to it, remove any other checkmarks, click Next
5. (Wizard Step 3 of 3) Click Finish

Now you should have parsed the original values into two separate columns - one for hours, one for minutes. If those columns are A and B, let's say.. in column C you could create the following formula to get time values: =Time(A1, B1, 0)
where
A1 is the hour
B1 is the minute
and the 3 argument (seconds) is a 'hard coded' 0. (sounds like you don't need seconds-precision in this example so 0 is fine).

Note, unless your original values were using military time everything is going to show up as 'AM'. If you don’t want to display AM or PM that’s an easy fix with number formatting. Choose the ‘Custom’ formatting option, then pick “hh:mm”

 

[xsil]

Don't know I'm late to this party but.. Did you try the following? (Especially if your colleague is not a "formula person" the following approach might be easier to explain, so next time they can fix it themselves!):

1. Select the values in question (ie the original times using ".")
2. Click Data > Text-to-Columns to launch the Text To Columns Wizard
3. (Wizard Step 1 of 3) Choose the 'Delimited' option, click Next
4. (Wizard Step 2 of 3) Check the 'Other' option on the left hand side, enter a . in the box next to it, remove any other checkmarks, click Next
5. (Wizard Step 3 of 3) Click Finish

Now you should have parsed the original values into two separate columns - one for hours, one for minutes. If those columns are A and B, let's say.. in column C you could create the following formula to get time values: =Time(A1, B1, 0)
where
A1 is the hour
B1 is the minute
and the 3 argument (seconds) is a 'hard coded' 0. (sounds like you don't need seconds-precision in this example so 0 is fine).

Note, unless your original values were using military time everything is going to show up as 'AM'. If you don’t want to display AM or PM that’s an easy fix with number formatting. Choose the ‘Custom’ formatting option, then pick “hh:mm”

-----
UPDATE:

Sorry, just noticed in your original post you DO in fact need seconds. In that case, your Text-to-Columns parsing will wind up with 3 columns, and the formula would look like this instead: =Time(A1, B1, C1)

 

[Chris Waller]

-----
UPDATE:

Sorry, just noticed in your original post you DO in fact need seconds. In that case, your Text-to-Columns parsing will wind up with 3 columns, and the formula would look like this instead: =Time(A1, B1, C1)

Thanks for your input. I have managed with a lot of help to sort the problem, however, whaen I get to work tomorrow I will try it out! Thanks once again!

 

[Vegas Spice]

I just found this thread as I am currently working on a similar problem. I input your formula and it worked, but gave an incorrect result. For example, the data I am working with is using numeric values for time, and my first value is 5.79. In real time, that would equal 5 munites and 47 seconds, but when I apply your formula is gives me 6:19. I understand why it did that, but would like to find a way to get the result I need.

I tried extracting the last two digits with the decimal point into a new column so I could multiply by 60, concatenate back together, but the output of my data is wonky and some numbers have one digit following the point and some have up to 6. Formatting the cells corrects it on the visible level, but not the underlying numbers.

I thought I would ask the experts on this board while I am working on it...

Thank you in advance!

 

[Andrew Poulsom]

You can use a formula like =A1/24 and format the cell as hh:mm.

 

[Vegas Spice]

You can use a formula like =A1/24 and format the cell as hh:mm.

Thank you, that worked! I'm never sure if I am happy or aggravated when the solution is much simpler than anticipated! I appreciate the feedback.