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 enter things weekly when do payroll. Showing, sick, vacation, personal etc.
It won't let me put a picture of it on here.
TRY:
=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...
- 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?
