Time Cells

[Guest]

I have a data dump that depicts 7:00 am as "700" and 5:00 pm as "500" (both
numbers.) I need to convert them to military time. Is there an easy way to
do this? When I try formatting the cells as time they all change to "00:00"
as excel attemps to convert the number into a date & time. Any guidance is
appreciated.

Thanks

 

[Dave Peterson]

put 2400 in an empty cell.
Edit|copy that cell
select the range to fix
edit|paste special|divide

Format those selected cells the way you want -- HH:MM (maybe).

Clean up that cell with 2400 in it.

 

[Guest]

Poptracker,

The 700 and 500 are indeed translated into dates, to be exact the day 700
days after 1/1/1900 and 500 days after 1/1/900, as excell sees 1 in date/time
format as a day.
To translate to time, if you only have full hours, devide by 2400 and then
format as time

If you have minutes as well as hours (ie 530 is 5:30), it is a bit more
complicated as you first have to split the hours and the minutes. For
example, use Right(A1,2) to get the minutes (30 in this case), deduct the
minutes from the original number to get the hours (500 in this case). Devide
the hours by 2400 (normally 24, but your hours are still notes as 100's),
devide the minutes by (60*24) and add them up; format as time, done

rdwj

 

[Guest]

or in one formula
=(F7-RIGHT(F7,2))/2400+RIGHT(F7,2)/(60*24)
with the time in cell F7

 

[Dave Peterson]

rdwj's comment about 730 returning 7:30 breaks my suggestion.

But another formula:

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

and format the result as hh:mm

 

[David Biddulph]

So how does your data dump distinguish between 6am and 6pm?