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 ...
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...
- Yes, I forgot to mention that 0:21 is 1 hour, but 1:21 is counted as 1 hour also (and everything above X:29 is rounded to next full hour, if X > 0. So first hour is counted as 1 hour nevertheless it is 15 min or 59 min, but everything more than 1 hour is subject to that if more than 29 minutes roundup to full hour.
I have a hard time wrapping my head around this. Could you provide some examples with the desired result?