Converting Cell Data to Time Data (QuiOui)

Posted by DSM on January 29, 2002 8:19 AM

I am currently in the process of importing a large amount of data from an AS400 server into an Excel 97 spreadsheet. Several columns contain time data in the format of 94636 (hmmss)for AM and 131426 (hhmmss) for PM. My problem is how can I convert those values into the military tiem format of 00:00:00 (hh:mm:ss)? Something to beat having to re-key each entry?! Any suggestions?

Posted by Ian Mac on January 29, 2002 8:38 AM

Messy, but could be cleaned up...

Assuming there's a value in a1 use:

=IF(LEN(A1)=5,(LEFT(A1,1)&":"&MID(A1,2,2)&":"&RIGHT(A1,2))+0,(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2))+0)

Format the cell to hh:mm:ss if needed

Ian Mac

Posted by Mark W. on January 29, 2002 8:46 AM

Use =SUM(MID(REPT(0,6-LEN(A1))&A1,{1,3,5},2)/{24,1440,86400})
and format as hh:mm:ss.

Posted by DSM on January 29, 2002 7:39 PM

Thanks to all! Simple (to some I guess), but a GREAT time saver to me!