Vacation days to restart on Anniversary date

Occasional Contributor

Good evening, 


I am trying to finish an excel sheet for my boss to restart vacation days to the employee's anniversary date. I have tried different formula's and can't get it. I keep getting error messages. I have the years of service, vacation days earned, and anniversary date calculated. Just need to generate a formula so that vacation days restart on employee's anniversary date. Can someone help me with this?

10 Replies


Can you post a print of the sheet?


And exactly do you want to do?

Do you check daily?



I enter things weekly when do payroll. Showing, sick, vacation, personal etc. 

It won't let me put a picture of it on here.

It sounds like you're trying to build a paid time off calculator (PTO). I'm guessing the PTO earned increases each year once the employee reaches the hire anniversary date.

For example:

An employee earns 8 hours of PTO every month for the first year. The second year that gets bumped up to earning 8.25 hours/month, for example.
There might also be a policy where one cannot carry over all PTO hours past a given year, etc. I think those are the kind of details needed to create a calculator here.


=LET(Adate, DATE( YEAR(TODAY()), MONTH(anniversary), DAY(anniversary)),
     Adays, TODAY() - Adate,
     IF( Adays>0, Adays, TODAY() - EDATE(Adate, -12)))

so Adate is the anniversary date THIS year and Adays is # of days from today - Adate. If that is negative (i.e. anniversary hasn't happened yet) then use today - anniversary date 12 months earlier

you can then convert # days to # weeks by /7 unless you have other nuances to the calculation...

No, I am trying to restart the number of days the individual gets. So, if employee A gets 20 days, I want it to restart on their anniversary date.
This will not work. I have the anniversary date. If employee A gets 20 days on their anniversary date, I want it to restart on their anniversary date. The thing is I have 4 quarters besides the summary page.
i think you need to explain a bit more exactly what you need. maybe upload a sample sheet to onedrive, sharepoint or dropbox and share a link. The formula I shared above addressed the main issue of finding if the most recent anniversary date is this year or last year and then from there you should be able to form what ever equation you need. For example maybe after finding Adate you want to do a 20-SUMIFS(vacation time, date, ">" & Adate) to show how much vacation time is left for them?

@mtarler Exactly. @Amber78 doesn't need to put the "real" data, just the sheet in blank with some examples.





First off if those are real names please remove immediately. I suppose if you post starting with Vacation Used column it would be ok. Maybe even if only first name was included. If the names were changed to fake name then that is good also.
In the above image it appears you need 2 (similar) formulas: one for days used and one for day earned.
As for how, I imagine it would something like:
=LET(A1date, DATE( YEAR(TODAY()), MONTH(anniversary), DAY(anniversary)),
Adate, IF(TODAY() - A1date >0, A1date, EDATE(A1date, -12)),
then add the formula to calculate earned days or sum of used days based on the anniversary date: Adate
As for the formula I don't know how you have those other tabs set up.
And speaking of those other tabs, I would highly recommend you combine then into a single tab and then you can use a 'reporting' tab with a pivot table or FILTER() formula to show each quarter and then you could do lots of other calculations and such across the year much easier.