Figuring out a formula

[jamesharris]

Happy Holidays everyone. I'm just stuck on trying to get the correct calculation/formula.


In the Attachment

A week is defined as 12:00am on a Saturday to 11:59pm on a Friday
· The schedule (starting with weeks 1-6) starts on a Saturday
· Weekend days are grey

What I need to adjust pertains to any N shifts at the end of each 3 week block
At the end of each 3 week block, if a staff person is working a 11.25 hour night shift, it starts on the Friday night but goes over to the next 3 week block on the Saturday

Right now, the formula is assigning all 11.25 hours to the Friday but it should assign 4.0 hours to the Friday and 7.25 hours to the Saturday.

For example, the dark orange line 14 on Weeks 1-6, ends week 3 with a N shift. The formula is adding all 11.25 hours in that first 3 weeks but it should only assign 4.0 hours on the Friday and 7.25 hours on the Saturday.

I've spent hours racking my brain trying to figure out the correct formula.

[#2]

Assuming I am not wrong:
From the image and the visible formula, I see no consideration of time or week numbers. This explains why 11.25 is added for the marked weekdays. If you could consider these variable, it might help you.
Alternately, you could use an IF statement for Fridays and Saturdays, forcing them to multiply with 4 and 7.25 respectively. Something like: IF(E13="F",COUNTIF($E$14:$Y$14,"D")*4,IF(E13="S",COUNTIF($E$14:$Y$14,"D")*7.25,COUNTIF($E$14:$Y$14,"D")*11.25))
You might want to use a little longer abbreviation or alternate symbol for the days, or Saturday and Sunday would be calculated the same.