Periodic cumulative sums of a single array, restarting every time a certain value appears

Hi All,

With reference to the attached.

Background
This is an overtime form I want to introduce at my company, to automate the calculation of overtime. Currently the employees manually write down their overtime hours in pen and submit to the payroll clerk. Payroll clerk is then responsible for allocating overtime hours to a 30% bracket, 50% bracket in accordance with the allocation rules set out in local law etc. So far there have been some gross errors made by the clerk and these haven't been in the company's favour (which is probably the least objectionable error as it is in favour of the employees and hence we don't have labour inspectors all over us). But this highlights a need to automate this allocation process on Excel, using the legal framework provided by law as the means by which hours are allocated.

Problem 1
1. Local law stipulates the first 8 hours of work are paid at normal rate
2. Local law stipulates that the first 45 hours of work per week are also at normal pay (it is important to note that in my country of operation, an 8 hour working day is standard on Monday - Friday, as is a five hour working day Saturday).
3. Local law also stipulates an exception to rule 1 above, in that the ninth hour of work in each day will not attract overtime provided that the total weekly working hours (Sun - Sat) does not exceed 45 hours.

Hence, it is important for me to be able to calculate the cumulative hours worked on a weekly (Sun - Sat) basis, so that I can apply rule 3 above i.e. exclude the ninth hour of work per day from the overtime calculation, if the weekly hours worked (Sun - Sat) do not exceed 45. This cumulative hours worked on a weekly basis are intended to be shown in column J.
Hence, I need a formula in cells J43 : J73 that is capable of taking the hours worked in a day (see column I) and adding those into a weekly total (again, Sunday to Saturday). However, each overtime sheet is for a complete month and rows 43 - 73 represent all the days in a given month, so the formula I need in column J must be capable of restarting a new cumulative count every Sunday.
To give an example, I have inserted a rigid formula in cells J43 : J54 to show what the totals would look like but this formula isn't fit for use as it is reliant on the days in column C staying static month to month (try changing the month in the drop down list in cell C28 and you will see the days change rows) hence I need a formula that is capable of detecting whether the day in column C is a Sunday ("Sun"); if so the summing of the weekly hours restarts, if not (and this is the element I'm struggling with) I need to command a cumulative count of weekly hours beginning from the last Sunday.

You will see from in cells J62:J73 I have attempted the beginning of a formula which may be capable of doing this, but as I just said, I'm struggling with the latter element.

Problem 2

In column K, I need to insert a formula that is capable of doing the following:
Generate a "Y" value if the value of hours worked on a given day (column I) exceeds 8 hours AND the cumulative hours worked for the week (which will be shown in column J on the nearest following Saturday) exceeds 45 hours (Y denoting "Yes, overtime needs to be paid on this ninth hour")
Formula will also generate an "N" if value in column I exceeds 8 hours AND the cumulative total on the next following Saturday is less 45 hours (N denoting "No, overtime need not be paid on this ninth hour")
Finally formula should generate a blank if both the value in column I is less than or equal to 8 hours AND the cumulative total on the following Saturday is less than 45 hours.
I will use the "Y" or "N" values to allocate overtime hours in column L onwards.

I have inserted an example in cell K43 (yellow cell) but again, this is formula is rigid and is not capable of detecting the nearest following Saturday. This formula also falls down (apparently!) because a value of 09:00 (cell I43) is not greater than the value of 8 given in the formula.

Appreciate this may be a difficult ask so thanks for your time and attention in advance.

There is no attachment.

For calculating weekly total:

helper in column X

X42=1

X43 and copy down

=IF($C43="Sun",MAX($X$42:X42)+1,MAX($X$42:X42))

in J43 and copy down:

=SUMIF($X$43:X43,X43,$I$43:I43)

Weekly overtime calculation

In K3

=IFERROR(IF(AND($I43>8/24,INDEX($J43:$J$73,MATCH("Sat",$C43:$C$73,0))>45/24),"Y",IF(AND(I43>8/24,INDEX($J43:$J$73,MATCH("Sat",$C43:$C$73,0))<45/24),"N",IF(I43<=8/24,"","Error"))),"")