Conditional Formatting to highlight sections in a table

Question

How to create a conditional formatting rule for a schedule so that if someone works more than 8 times within 14 days throughout a month, it gets highlighted. For example, If someone works 9 times from the date 4/3 - 4/17, it would highlight that section in that row to show what part of the schedule exceeded 8 days.

Tried to mess with the conditional formatting tab but could not understand it enough. Would result in highlighting entire rows or rows without the variable that was being counted.

The highlighted in the link below is what I want the format to highlight.

Example

What its supposed to do

Answer 1

Here/screenshot(s) refer:

Conditional formatting function:

=--(SUM(--(C7:$AG7="d")*(--C$6:$AG$6<=--C$6+MIN(EOMONTH($C$6,0)-$C$6+1,$B$4-1)))>$B$3)

---

Checks

=--(COUNTIFS(C7:OFFSET(C7,0,13),"d")>8)

---

Notes

• max: 3 in first function; 8 in checks

• rolling: Q stated 14, change as desired

• Assumes full month (hence eomonth above), change to required end period as req.

• Further refinement req. if 14 day rolling period not meant to span 2 different months/years etc.

• Cannot use reference function (e.g. offset) in conditional format equation for some silly reason - hence refined approach in checks not used initially

• Number format applied to checks section: see screenshot below

  "d";; * ""

---