Time formatting

[adamodonohue]

Hi there,

I need help. I am trying to enter times into a worksheet with raw data: 1630 , (no punctuation/colons) to give me result 16:30. I have tried formatting the cell with 00/:00 which works but it needs to be 24hr format so I can subtract one cell from another and still get an answer in 24hr format. If I try hh/:mm the formula bar comes up with 17/06/1904 00:00:00. I don't know why it's giving me a date. I don't need it.

Thanks in advance

[#2]

Hi @adamodonohue,

Have you tried hhmm

[#3]

@adamodonohue 

In Excel dates are integers where starting from Jan 01, 1900 (which is equal to 1). Time is decimal part of the number (1 hour = 1/24).

Thus value 1630 in terms of date will be 1630th day from 01/01/1900, or June 17, 1904. Since no decimal part was added it shows 00:00 time.

You can't convert  dates (integers)  to time (decimals) just by formatting, only by formulas.