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.
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.
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 this format:
'00 =12:00 AM
'03 = 12:30 AM
123 = 12:30 PM
133 = 1:30 PM
233 = 11:30 PM
HTH
Bruce
The older I get, the better I used to be.
USA
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks