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 erro...
excel
Formulas and Functions
CarlosCerqueira80's avatar
CarlosCerqueira80
Copper Contributor
Nov 10, 2022

Amber78 

Can you post a print of the sheet?

 

And exactly do you want to do?

Do you check daily?

 

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

      • Amber78's avatar
        Amber78
        Copper Contributor
        Nov 11, 2022
        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.
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Nov 11, 2022

      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.

      • Amber78's avatar
        Amber78
        Copper Contributor
        Nov 11, 2022
        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.