Keegan4123
New Member
- Joined
- Mar 30, 2022
- Messages
- 29
- Office Version
- 2016
- Platform
- 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.
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 |