if value is in B9: =LEFT(TEXT(B9,"00####"),2)&":"&MID(TEXT(B9,"00####"),3,2)
P Paul Corrado Aug 26, 2003 #2 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
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
