Convert time

[jr]

if value is in B9:
=LEFT(TEXT(B9,"00####"),2)&":"&MID(TEXT(B9,"00####"),3,2)

 

[Paul Corrado]

This may work better as it returns the time as a value.

=IF(LEN(B9)=6,TIMEVALUE(LEFT(B9,2)&":"&MID(B9,3,2)&":"&RIGHT(B9,2)),TIMEVALU
E(LEFT(B9,1)&":"&MID(B9,2,2)&":"&RIGHT(B9,2)))

However, it assumes that this is military time and 25400 is 2:54 AM.

Format the cell as Time.

PC