Working Schedule - Nurse Style

[alder]

Hi everybody. I searched the forum to see if there was any other threads with this kind of problem but couldn't find anything.

I am working on a work schedule, "nurse style" that has certain rules.
For this schedule, these are the following rules:
"Hours of service in any sliding 7 day period is to be no more than 65 hours. After reaching the HOS limit in any sliding 7 day period the controller must have at least 35 hours off. If HOS limit is reached the Relief/Support Controller or extra controller will fill in during controller’s 35 hours off. HOS should never be exceeded as a direct result of a controller engaging in non-control room activities."

So, my question is: Can I create some kind of a check that checks for those rules. Maybe a cell where I put in a period of time, let's say from A1: to D8, if there is someone working more than 65 hours, there is a conditional formatting that makes that period red.

Hope you guys understand my problem and if there is anyone that can help or direct me toward the right correction I would be very thankful.
Here is the start of my working schedule that is based on a template I found.

All the best,
Niklas

 

[Chihiro]

Oi, I'd strongly recommend separating out report layout for viewing (i.e. your schedule sheet), and data structure for input/storage.

Data input (or storage) should always be done in flat table structure (i.e. Date, Shift, Hours, Dept, Employee, Week ending columns).

This will make it so much easier to implement your validation rules (via code or via helper columns).