Time Cells
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 |
Time Cells
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. PopTracker wrote: 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 |
Time Cells
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 "PopTracker" wrote: 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 |
Time Cells
or in one formula
=(F7-RIGHT(F7,2))/2400+RIGHT(F7,2)/(60*24) with the time in cell F7 "PopTracker" wrote: 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 |
Time Cells
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 Dave Peterson wrote: 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. PopTracker wrote: 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 -- Dave Peterson |
Time Cells
So how does your data dump distinguish between 6am and 6pm?
-- David Biddulph "PopTracker" wrote in message ... 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 |
All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com