Formula for grouping different work hours

[BJSOON]

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!

[kylemhabe · Accepted solution]

Try this out.