Change numbers into time

[gdelmonico]

Hello all,

Quick question, I had query through AS400 download to an excel spreadsheet one of the columns was for a time.

I need to change the format of how it came out on the spreadsheet

Example:

In A2 - 30826 ----This should read 3:08:26am

Is there any way to convert this?
Any help would be great and please let me know if you need anymore explanation.

[Sam Capricci]

this would be one way...
=TIMEVALUE(IF(LEN(A2)=5,LEFT(A2,1)&":"&MID(A2,2,2)&":"&RIGHT(A2,2),LEFT(A2,2)&":"&MID(A2,3,2)&":"&RIGHT(A2,2)))
then format as time

[gdelmonico]

Amazing!!!!

Thanks for your help

[Sam Capricci]

You're welcome! Glad that solved it for you. And thank you for the rep.

[XLent]

FWIW, you could also use the below, a little shorter / simpler

Please Login or Register  to view this content.