Calculating Days off - Repost, changed system

Keegan4123

New Member
Joined
Mar 30, 2022
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Today is20/06/2022
2MonthDays Left Before Mandatory 4 day ResetExplanation comments, wont be in spreadsheetJun 15Jun 14Jun 13Jun 12Jun 11Jun 10Jun 9Jun 8Jun 7Jun 6Jun 5Jun 4Jun 3Jun 2Jun 1May 31May 30May 29May 28May 27May 26May 25May 24May 23
3Staff 1 (as it should be)14Had 4 days off prior to working 10, so has 14 left 111111111111111111
4Staff 1 (incorrect)142 days off in middle doesn’t reset previous days, so actually only has 2 days left before they have to reset1111111111111111111111
5Staff 1 (incorrect)17Single days off don’t reset cause its more than 6 so only has 2 days left 1111111111111111111111
6
7
8Rules:Days off must be consecutive
91 day off for every 6 worked
102 days off for every 12 worked
113 days off for every 18 worked
124 days off for every 24 worked
13
14
15
DaysWorked
Cell Formulas
RangeFormula
B1B1=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2,D2:XFD2Cell Value=$B$1textNO
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,350
Messages
6,171,592
Members
452,412
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top