Time Conversion

[fallof89]

I have been attempting to convert numbers such as '080952' to 08:09:52 am. I have tried time conversion by formatting cells, and when I do I receive 0 or 12:00am.

I have used the following formula:

=TIME(VALUE(LEFT(B2,2)),VALUE(MID(B2,3,2)),VALUE(RIGHT(B2,2)))

and it works well for the most part, but there are cells that aren't converted properly.


I also tried to format the cells initially to ensure six digits, from '42515' (4:25:15 am) to '042515' to see if this would help and it didn't. I'm sure I'm missing something.

Please help.

 

[Weazel]

you could try something like...

=TEXT(A1,"00\:00\:00")+0

than format the cell as time

 

[fallof89]

That actually worked. Thanks Weazel. Not to sound ungrateful, but is it possible to add am/pm? If not, no biggie.

 

[Weazel]

you should be able to have the AM/PM with regular formatting

if you select the cell and press control 1....Number....Time....then select the format you want the time in

 

[fallof89]

I simply overlooked that. Duh on my behalf. Thanks Weazel.

 

[HappsLucks]

This might work for you.
A1 : 080952 [format as text]
A2 : =INT(LEFT(A1,2)) [format as number]
A3 : =IF(A2<12,"AM","PM")

Result : =TEXT(A1,"00\:00\:00")&" "&A3 [format as time]