Calculate Vacation dates and entitlements

I am trying to figure out a formula for vacation entitlements example below

Years of service 0-4 is 10 days

Years of service 5-9 is 15 days

Years of service 10-14 20 days

Years of service 15 and is max and does not matter if it hits 100 years  25 Days

Best Answer

  • James Harris
    James Harris ✭✭✭
    Answer ✓

    Assuming you have a column called "Years Of Service" with the numerical value for the number of years of service, you can have an Entitlement Days column with a column formula of:

    =IF(ISNUMBER([Years Of Service]@row), IF([Years Of Service]@row < 5, 10, IF([Years Of Service]@row < 10, 15, IF([Years Of Service]@row < 15, 20, 25))), "")

    Thanks,

    James

    CTO, Smarter Business Processes

Answers

  • James Harris
    James Harris ✭✭✭
    Answer ✓

    Assuming you have a column called "Years Of Service" with the numerical value for the number of years of service, you can have an Entitlement Days column with a column formula of:

    =IF(ISNUMBER([Years Of Service]@row), IF([Years Of Service]@row < 5, 10, IF([Years Of Service]@row < 10, 15, IF([Years Of Service]@row < 15, 20, 25))), "")

    Thanks,

    James

    CTO, Smarter Business Processes

  • Thanks so much it worked like a charm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!