Hello All,
I am fairly new to an administrative roll and am currently trying to finish an attendance track I have have started and I am stumped right now (excel novice).
So basically our the two attendance rules at work are as follows;
1. You have 6 Sick occurrences to spend in a rolling calendar year. Anymore more than this and you will be put into our attendance management program.
2. Lates are tracked back to the last 6 months.
The formula I need to come up with is to keep track of these occurrences in the following year because we make a separate excel book for every year. Currently I have made a sheet that I will just fill out at the beginning of every year that will show the absences by date of the previous year. I would like the formula to sum up all relevant dates in that row to show the employees current occurrence sum. I hope I am making sense.
For example;
If Employee A calls in sick Dec 3 2017, it will not come off their sick occurrence sum until Dec 4 2018. Or If Employee A is late December 3 2017, it will not come off their late occurrence sum until June 4 2017.
Thanks in advance for all suggestions and help.
I am fairly new to an administrative roll and am currently trying to finish an attendance track I have have started and I am stumped right now (excel novice).
So basically our the two attendance rules at work are as follows;
1. You have 6 Sick occurrences to spend in a rolling calendar year. Anymore more than this and you will be put into our attendance management program.
2. Lates are tracked back to the last 6 months.
The formula I need to come up with is to keep track of these occurrences in the following year because we make a separate excel book for every year. Currently I have made a sheet that I will just fill out at the beginning of every year that will show the absences by date of the previous year. I would like the formula to sum up all relevant dates in that row to show the employees current occurrence sum. I hope I am making sense.
For example;
If Employee A calls in sick Dec 3 2017, it will not come off their sick occurrence sum until Dec 4 2018. Or If Employee A is late December 3 2017, it will not come off their late occurrence sum until June 4 2017.
Thanks in advance for all suggestions and help.
