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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 11, 2022
        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?
  • 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.