Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate between two time formats
i I am working on a spreadsheet, where I need to know how to Calculate
between two time formats, i.e. I want the cell to calculate between 06:00 and 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and finished at 07:00 the following day, I need it to tell me how may hours before 18:00, then how many hours between 18:00 and 06:00, the how many hours after 06:00. If some one could help. Thanks John |
#2
|
|||
|
|||
A2: Start time
B2: End time Time before 18:00: =IF(A2--"18:00",0,"18:00"-A2) Time between 18:00 and 6:00: =MIN(--"6:00",B2)-MAX(--"18:00",A2)+(MIN(--"6:00",B2)<MAX (--"18:00",A2)) Time after 6:00: =IF(B2<--"6:00",0,B2-"6:00") This of course assumes that the start time is always in the PM and the end time is always in the AM. HTH Jason Atlanta, GA -----Original Message----- i I am working on a spreadsheet, where I need to know how to Calculate between two time formats, i.e. I want the cell to calculate between 06:00 and 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and finished at 07:00 the following day, I need it to tell me how may hours before 18:00, then how many hours between 18:00 and 06:00, the how many hours after 06:00. If some one could help. Thanks John . |
#3
|
|||
|
|||
To get the night shift hours (18:00 - 06:00)
With start time in A1 and end time in B1 06:00 in A2 and 18:00 in B2 to get the night shift hours =MAX(0,MIN(B1,A2)-IF(B1A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1)-IF(B1A1,1-B 1,MIN(0,B2-B1))) to get the day shift hours =MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1 )-IF(B1A1,1-B1,MIN(0,B2-B1)))) -- Regards, Peo Sjoblom "john86" wrote in message ... i I am working on a spreadsheet, where I need to know how to Calculate between two time formats, i.e. I want the cell to calculate between 06:00 and 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and finished at 07:00 the following day, I need it to tell me how may hours before 18:00, then how many hours between 18:00 and 06:00, the how many hours after 06:00. If some one could help. Thanks John |
#4
|
|||
|
|||
I have an example on my website which handles many scenarios (including
spans over midnight) Hours affected by dates -- Rob van Gelder - http://www.vangelder.co.nz/excel "john86" wrote in message ... i I am working on a spreadsheet, where I need to know how to Calculate between two time formats, i.e. I want the cell to calculate between 06:00 and 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and finished at 07:00 the following day, I need it to tell me how may hours before 18:00, then how many hours between 18:00 and 06:00, the how many hours after 06:00. If some one could help. Thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate time difference to the half hour | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
formula to calculate a time sheet | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) |