Hi I need help with a formula. I need to capture the daily overtime hours into 2 buckets. the rules are:
- first 3 hours outside of the daily ordinary working hours
- subsequent hours outside of the daily ordinary working hours
Ordinary working hours is defined as between 0700 hour to 1900 hour.
Individual employee time sheets are entered on a daily basis and there can be multiple entries per day . Example:
| Date | Start Time | End Time | Time Entry Type |
| 2 Mar 2020 | 08:00 | 12:00 | Working Hours |
| 2 Mar 2020 | 12:00 | 13:00 | Lunch break |
| 2 Mar 2020 | 13:00 | 21:00 | Working Hours |
| 3 Mar 2020 | 06:00 | 12:00 | Working Hours |
| 3 Mar 2020 | 12:00 | 12:30 | Lunch break |
| 3 Mar 2020 | 12:30 | 23:00 | Working Hours |
In the example above, employee clocked 2 hours of overtime on 2 March under "first 3 hours".
On 3 March, employee clocked 5 hours of which 3 hours fall under "first 3 hours" and 2 hours under "subsequent hours". How do I reflect that in the workflow?
Thank you in advance!
