Forum Discussion
Vacation days to restart on Anniversary date
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 erro...
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?
- 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.
