Calculating Days off - Repost, changed system

[Keegan4123]

So we have a running excel sheet that tracks when an employee works. We were using google sheets but it wont work so switched to excel for the final version.

We run under labor laws that allow for a maximum of 24 days worked straight, and then require 4 consecutive days off. Essentially its 1 day off for every 6 days worked. they must be consecutive to reset the clock.

So you can work a 6 and 1 or a 12 and 2, but could not work a 12 and 1, and then a 12 and 2. this would put them over their days because the 1 day off would not have reset the clock so they are technically on day 25 and would need 4 off consecutive to reset.

I have a mini sheet attached in excel, for figuring it out, The biggest concern in the mini sheet is the formula in column B. The sheet will be auto filled as days go buy with 1 for a day worked and blank for a day off. I will also be creating columns for every day of the year and hiding them.

I initially tried doing a simple sumif to get the total days worked for a period that summed the days between today and 28 days prior. if the number went over 24, we had an issue, but looking at a few that flagged, they were still in compliance.

Days Worked 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Today is	20/06/2022
2	Month	Days Left Before Mandatory 4 day Reset	Explanation comments, wont be in spreadsheet	Jun 15	Jun 14	Jun 13	Jun 12	Jun 11	Jun 10	Jun 9	Jun 8	Jun 7	Jun 6	Jun 5	Jun 4	Jun 3	Jun 2	Jun 1	May 31	May 30	May 29	May 28	May 27	May 26	May 25	May 24	May 23
3	Staff 1 (as it should be)	14	Had 4 days off prior to working 10, so has 14 left	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1			1
4	Staff 1 (incorrect)	14	2 days off in middle doesn’t reset previous days, so actually only has 2 days left before they have to reset	1	1	1	1	1	1	1	1	1	1			1	1	1	1	1	1	1	1	1	1	1	1
5	Staff 1 (incorrect)	17	Single days off don’t reset cause its more than 6 so only has 2 days left	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1		1	1	1	1	1	1	1
6
7
8			Rules:	Days off must be consecutive
9			1 day off for every 6 worked
10			2 days off for every 12 worked
11			3 days off for every 18 worked
12			4 days off for every 24 worked
13
14
15
DaysWorked

Cell Formulas
Range	Formula
B1	B1	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2,D2:XFD2	Cell Value	=$B$1	text	NO

 

[KRice]

At what moment does the requirement for a day off go into effect? If I worked 6 days, I would need 1 day off unless I continued working, and at some point I would need more days off to reset my 24-day clock. Would I incur the requirement for a 2nd day off when I began working on the 7th day, or does that requirement go into effect only after the 12th work day?

I believe a formula-based solution for this will involve a number of helper cells. Realistically, what would be the largest number of sets of days off (where a set is some number of continuous days off) during the 24-day assessment period?

 

[Keegan4123]

At what moment does the requirement for a day off go into effect? If I worked 6 days, I would need 1 day off unless I continued working, and at some point I would need more days off to reset my 24-day clock. Would I incur the requirement for a 2nd day off when I began working on the 7th day, or does that requirement go into effect only after the 12th work day?

I believe a formula-based solution for this will involve a number of helper cells. Realistically, what would be the largest number of sets of days off (where a set is some number of continuous days off) during the 24-day assessment period?

So the way the law is written is that 1 day off for every 6 works means, as you said, if you worked 7, you would need 2 off to fully reset. If you worked 7, took 1 off, then back to work, you would technically not be reset. But its a tough one cause then the question is raised of well does the 1 day reset the first 6, or does it become null and not reset any? Regardless I am not looking for that level of detail on the staged days because they are less important. The biggest issue and known hard rule is you cannot work 25 days straight. you are limited to 24 days straight and 4 off.