Converting 6 digits to time in Excel 2003?

Question

I have a large spreadsheet each day that lists 24 hour times:
     9:45:24 am renders as 94524
     9:45:24 pm renders as 214524

I need to do a large number of calculations between times:
     121824 minus
     120000

If the colons were in place for the time, Excel would recognize it and subtract correctly.  The problem is every formula I attempt in excel (ie hh:mm:ss) comes out as 0:00:00

What is the proper formatting so that I can turn 121824 into 12:18:24?

I will be so excited if someone can help me!!!  Thanks in advance!

expletive_xom · Accepted Answer

this is not as easy as it seems like it should be

put your time in A1
copy and paste this formula in A2
you might have to paste it in notepad to carefully put it together, then cut&paste into excel

=IF(A1<100000,(
LEFT(A1,1)&":"&
MID(A1,2,2)&":"&
RIGHT(A1,2))+0,(
LEFT(A1,2)&":"&
MID(A1,2,2)&":"&
RIGHT(A1,2))+0)

change the format of cell A2 to a time format

basically,
the lefts look for hours, mids look for minutes, and rights look for seconds.  

the amount of digits was throwing me, but i went with the if() as opposed to len() for no particular reason

did some have 6 digits,and other have 5 digits? your sample had both, but your question says 6
are these 6 digits formatted as numbers or text?i assumed general or numbers

if this doesnt work, please rpovide more detail
good luck

Kolawole · Answer

On the Home tab, in the Cells group, click Format, and then click Format Cells.
In the Format Cells dialog box, click the Number tab.
Under Category, click Custom.

In the Type box, type [h]:mm.
Tip   You can also show the results in minutes by setting the format to [m]:ss.

Click OK.
If you used the example data (12:45 and 15:30), cell A3 will display 28:15 (or 1695:00 if you used the format [m]:ss).

From the extracts from MS Excel Help, you can see what you need to do. Note that the formatting is very flexible, and the likely effect of the use or othrwise of ":" (colon) in data entry in your workbook. Pls feel free to get back to me if necessary. Good luck

? · Answer

Well one instant thought is to save a document with the macro in it, in Excel 2003 mode. It makes sense that it would convert the macro to 2003 style in doing so. Choose file / save as... and look for the selector box for "save as type..." select excel workbook 2003 If that doesn't work I'm all out of suggestions.

Trending News

Converting 6 digits to time in Excel 2003?

3 Answers