convert military hours

[dmeehling]

I have columns of data entered in military time that I need to convert
as follows: convert 2330 to 11:30 p.m. Using format, cell, time
changes everything to 12:00 a.m.

 

[Dave Peterson]

Can you use a helper column?

=TIMEVALUE(TEXT(A1,"00\:00"))

and format the way you like.

Excel stores dates as numbers starting from a base date (usually December 31,
1899 for windows users). So when you put 2330 in a cell, xl saw that as 2,330
days after 12/31/1899 (May 18, 1906).

And excel stores time as fractions of a day: 1:00 AM is 1/24. 12 Noon = 12/24
= .5.