Calculating Days off required

[Keegan4123]

So we have a running google sheet that tracks when an employee works. 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, but the ultimate sheet will be in google and allow the workbook to update with days worked automatically. The biggest concern in the mini sheet is the forumula 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	15/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

 

[Fluff]

Duplicate to: Calculating Days off - Repost, changed system

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.