Attendance Spreadsheet with resetting values on a particular date

ScousePete

New Member
Joined
Feb 10, 2008
Messages
33
Office Version
  1. 365
Platform
  1. 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?

Cell Formulas
RangeFormula
B4B4=NOW()
B5:C9B5=ALL_NAMES
D6:D9D6=IF(C6="-","-",$B$4-C6)
E6:E9E6=IF(B6="-","-",DATE(YEAR($C6)+DATEDIF($C6,$B$3,"y")+1,MONTH($C6),DAY($C6)))
F6:F9F6=IF(B6="-","-",DATEDIF($B$4,E6,"d"))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Names!_FilterDatabase=Names!$C$1:$D$43B5:C9
ALL_NAMES=Names!$C$1:$D$100B5:C9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AU8:AY104,AV6:BA6,AV7:AY7,AZ7:BA104,B6:AT104Expression=MOD(ROW(),2)=0textNO
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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