MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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