Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
How can I caculate military times such as 1800 hours to 400 hours for a time
sheet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
Hi Tom,
Two steps: 1) Enter time with a colon separating the hours and minutes (18:00 or 4:00), and 2) Format the cells in military time {Press Ctrl 1 or go to the Format Menu - Cells - Number tab - Time [select the military format you want (ex: 13:00) - press OK]}. Hope this helps. "Tom" wrote: How can I caculate military times such as 1800 hours to 400 hours for a time sheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
=B21-A21+(B21<A21)*2400
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom" wrote in message ... How can I caculate military times such as 1800 hours to 400 hours for a time sheet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
Bob,
I should learn how to use Boolean instead of IF( ). Case in point +(B21<A21)*..... If B21 = A21, there is no way to tell if it is the next day or the same day. I guess depending on the user's need, one may have to change the formula to =B21-A21+(B21<=A21)*2400 to reflect 2400 for the lapse of one day. Any comments? Epinn "Bob Phillips" wrote in message ... =B21-A21+(B21<A21)*2400 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom" wrote in message ... How can I caculate military times such as 1800 hours to 400 hours for a time sheet? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
"Epinn" wrote in message ... Bob, I should learn how to use Boolean instead of IF( ). Case in point +(B21<A21)*..... If B21 = A21, there is no way to tell if it is the next day or the same day. I guess depending on the user's need, one may have to change the formula to =B21-A21+(B21<=A21)*2400 to reflect 2400 for the lapse of one day. The formula already has an algorithm to determine whether it goes over midnight, that is whan the +(B21<=A21)*2400 is doing, it determines that it goes over midnight if the end time is earlier than the start time, on the basis that the task will not be greater than 24 hours. I suppose you could argue he might start at 8:00 and finish at 8:00. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
=MOD(B1-A1,2400)
Tom wrote: How can I caculate military times such as 1800 hours to 400 hours for a time sheet? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I caculate 24 hour military times
The above fomulae (Bob's and mine) only work for hourly times as in the
example given. But for other times an adjustment is needed eg 2115 to 800 should give 1045 (not 1085). This should work for all times entered in "hhmm" format: =--TEXT(MOD(TEXT(B1,"0\:00")-TEXT(A1,"0\:00"),1),"hhmm") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a formula to muliply hours times dollars per hour? | Excel Discussion (Misc queries) | |||
How to I convert standard time to Military or 24 hour format? | Excel Discussion (Misc queries) | |||
How do I sum times to get a total hour value? If I add up to 24 h. | Excel Discussion (Misc queries) | |||
subtracting times using 24 hour clock | Excel Worksheet Functions | |||
how do you subtract military time? example 12:00 - 07:00 = 5 hour. | Excel Worksheet Functions |