Forum Discussion

Amber78's avatar
Amber78
Copper Contributor
Nov 10, 2022

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 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?

excel
Formulas and Functions

10 Replies

    • Amber78's avatar
      Amber78
      Copper Contributor
      Nov 11, 2022

      CarlosCerqueira80 

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

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

      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 11, 2022

        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...