convert number to time format?

J

JLHORMAN

I have a column of 3-4 numbers representing time of day. The number vary
from 63 (=6:30 AM) to 113 (= 11:30 AM). Can anyone help me to convert
the numbers to time format? Thanks.
 
S

swatsp0p

Assumption:

Cell D7 contains your coded time (e.g. 63 or 113)

Use this formula in a cell formatted as TIME 1:30 PM:

=(LEFT(D7,LEN(D7)-1)&":"&RIGHT(D7,1)&"0")*1

The result is 63 becomes 6:30 AM
113 becomes 11:30 AM

NOTES:
times from midnight to 12:50 AM will need to be entered as text in thi
format:
'00 =12:00 AM
'03 = 12:30 AM
123 = 12:30 PM
133 = 1:30 PM
233 = 11:30 PM

HT
 
Top