Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting cells for time | Excel Discussion (Misc queries) | |||
formatting cells for time | Excel Discussion (Misc queries) | |||
Elapsed time when separate cells contain time and separate date | New Users to Excel | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions | |||
add time to cells | Excel Worksheet Functions |