ScousePete
New Member
- Joined
- Feb 10, 2008
- Messages
- 33
- Office Version
- 365
- Platform
- MacOS
I am creating a spreadsheet for my department to track the number of times an employee calls in sick.
Each employee gets 6 sick days per year, which resets on their anniversary date
I have created a sheet for each month and entered each employee, together with their anniversary date. There are 28-31 columns, depending on the month, with the idea that a manager enters an X in the cell and then a formula calculates how many sick days the employee has remaining. I am fine with all this. The challenge I have is that on their anniversary date the number needs to be reset back to six. I could connect it to today's date, but I need it to continue counting after the reset. Any pointers?
Each employee gets 6 sick days per year, which resets on their anniversary date
I have created a sheet for each month and entered each employee, together with their anniversary date. There are 28-31 columns, depending on the month, with the idea that a manager enters an X in the cell and then a formula calculates how many sick days the employee has remaining. I am fine with all this. The challenge I have is that on their anniversary date the number needs to be reset back to six. I could connect it to today's date, but I need it to continue counting after the reset. Any pointers?
Attendance.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
4 | 8/13/2022 | ||||||
5 | NAME | DOH | DAYS OF EMPLOYMENT | Next Anniversary | Days until | ||
6 | John Smith | 6/18/2019 | 1152 | 06/18/23 | 309 | ||
7 | John Doe | 11/20/2006 | 5745 | 11/20/22 | 99 | ||
8 | Bill Jones | 11/21/2006 | 5744 | 11/21/22 | 100 | ||
9 | Mary Williams | 1/21/2020 | 935 | 01/21/23 | 161 | ||
August 2022 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4 | B4 | =NOW() |
B5:C9 | B5 | =ALL_NAMES |
D6:D9 | D6 | =IF(C6="-","-",$B$4-C6) |
E6:E9 | E6 | =IF(B6="-","-",DATE(YEAR($C6)+DATEDIF($C6,$B$3,"y")+1,MONTH($C6),DAY($C6))) |
F6:F9 | F6 | =IF(B6="-","-",DATEDIF($B$4,E6,"d")) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Names!_FilterDatabase | =Names!$C$1:$D$43 | B5:C9 |
ALL_NAMES | =Names!$C$1:$D$100 | B5:C9 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AU8:AY104,AV6:BA6,AV7:AY7,AZ7:BA104,B6:AT104 | Expression | =MOD(ROW(),2)=0 | text | NO |