Forum Discussion
Overlaping of worktimes
| A | B | C | D | E | F |
| Start of overtime | End of overtime | Total overtime | Start of Stand-by | End of Stand-by | Total Standby |
| 0:00 | 1:30 | 2 | 7:00 | 19:00 | 12 |
| 7:00 | 20:00 | 13 | 7:00 | 19:00 | 0 |
| 20:00 | 23:50 | 4 | 19:00 | 0:00 | 1 |
| 5:00 | 8:30 | 4 | 7:00 | 19:00 | 10 |
| 7:00 | 19:00 | 12 |
Hello,
above is example sheet that I use for workers. This is part with standby and overtime, and I need to decrease time of Total Standby with hours of Total overtime only if times overlap, like in table above (if worker is on overtime for 30 and more minutes it is counted as full hour). Overtime can be any hour between 0 - 24h because worker can be called at any time for work in case of emergency (firefighters), but standby is entered only in this way (each row represent new day):
| D | E | F |
| Start of Stand-by | End of Stand-by | Total Standby |
| 19:00 | 0:00 | 5 |
| 0:00 | 7:00 | 7 |
| 7:00 | 19:00 | 12 |
7 Replies
In F2:
=(E2-D2+(E2<D2))*24-ROUNDUP(MAX(MIN(B2+(B2<A2), E2+(E2<D2))-MAX(A2, D2), 0)*24, 0)
If you use comma as decimal separator, use semicolon ; instead of comma in the formula.
Apply General number format to F2, then fill down.
HansVogelaar Thank you! I just used round instead of roundup because roundup rounds 1:10 as 2 hours instead of 1 hour. Currently have only issue with round if time difference is less than 1 hour (example 0:21 min) it leaves it as 0 hours instead of 1 hour.
tomislavnadj You mentioned that 30 minutes and up is rounded to 1 hour.
21 minutes is less than 30 minutes...
