Forum Discussion

Kyle_Doering's avatar
Kyle_Doering
Copper Contributor
Dec 18, 2023

VACATION DAYS EXCEL FORMULA

I am trying to make an excel formula to calculate PTO days. Our employees get a prorated amount on their anniversary date and then everything resets on Jan 1. For example if they started July 2, 2022 then on July 2, 2023 they get a prorated total of 3 days and then on Jan 1, 2024 it would reset and they would get a new 5 days. They would then get 5 days on each Jan 1 until they reach their 3 year anniversary date and they would then get a prorated 8 days until the next Jan 1 where they would get a new 10 days going forward every Jan 1

excel
Formulas and Functions

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 19, 2023

    Hi Kyle_Doering 

     

    You did not mention the Excel version in used => 2021 or 365 is assumed:

     

    In attached file play with Simul. Today's date and check this gives you what you expect (column [Days] in green table) given the [Start date] of David, Ella...John

    • Kyle_Doering's avatar
      Kyle_Doering
      Copper Contributor
      Dec 19, 2023
      Good morning, Lorenzo Yes I am using 365. is there a way to prorate days for the time in between their anniversary date and Jan 1 when all vacation resets?
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 19, 2023

        Kyle_Doering 

         

        Is there a way to prorate days for the time in between their anniversary date and Jan 1 when all vacation resets?

        Maybe. Could you post a picture with a couple of expected results and explain how you calc. those results please?

Resources