Change a number to time
Posted by Kerry on September 18, 2001 2:52 PM
I have a column of data that I need to convert in to a time formet. Currently the data is
like this.
A
10652
9545
10037
03
1
Example if A2 has the number 10652, 106 represents the total hours and 52 represents the minutes.
I need it formated like 106:52:00. I need to incert the : colons and add the zero. I have tried
changing the format and it thinks it is a serial number, which it is not. Can anyone help with this. Either a formula or VBA!
Posted by Tom Urtis on September 18, 2001 3:11 PM
Here's one way to do it
Two step process:
Step 1: highlight range in column A, then click Format > Cells > Number tab, choose Custom in the Category pane, then type in 00000 in the Type box.
Step 2:
In B2, enter:
=LEFT(A2,3)&":"&RIGHT(A2,2)&":00"
and copy down as needed.
Assumes your column A entries are no more than 5 digits long.
HTH
Tom Urtis
Posted by KERRY on September 18, 2001 3:22 PM
Re: Here's one way to do it
WHAT ABOUT WHEN THE NUMBER IS 1 OR 03?
Posted by WA on September 18, 2001 4:05 PM
What results would you expect with these? (NT)
Posted by Tom Urtis on September 18, 2001 4:32 PM
OK, here is revised formula
Enter this in A2 and copy down as needed:
=IF(LEN(A2)=5,LEFT(A2,3)&":"&RIGHT(A2,2)&":00",IF(LEN(A2)=4,"0"&LEFT(A2,2)&":"&RIGHT(A2,2)&":00",IF(LEN(A2)=3,"00"&LEFT(A2,1)&":"&RIGHT(A2,2)&":00",IF(LEN(A2)=2,"000"&":"&RIGHT(A2,2)&":00","000:0"&RIGHT(A2)&":00"))))
Tom Urtis
Posted by Tom Urtis on September 18, 2001 4:34 PM
Correction, enter the formula in B2 and copy as needed
