Calculate Vacation Days from Years of Service in Excel
Question details
Calculate an employee's vacation entitlement from their hire date and completed years of service.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs to assign vacation days at service milestones such as five, twelve, and eighteen years.
- Observed behavior
- The desired result is a vacation-day amount that changes when the employee reaches each applicable service threshold.
Enter the employee's hire date as a valid Excel date in D4 and confirm that it is not in the future. Check your organization's service thresholds and vacation amounts; the amounts below are examples.
Use a Nested IF Formula for Vacation Tiers
Use this formula when the vacation policy has a few fixed service thresholds.
The example assigns 10 days before five years, 15 days from five years, 20 days from twelve years, and 25 days from eighteen years. It checks the highest threshold first so employees receive the correct tier.
Type the employee's hire date in cell D4 and format it as a date using Home > Number Format.
Select the vacation entitlement cell and enter =IF(DATEDIF(D4,TODAY(),"y")>=18,25,IF(DATEDIF(D4,TODAY(),"y")>=12,20,IF(DATEDIF(D4,TODAY(),"y")>=5,15,10))).
Replace the thresholds and day amounts in the formula with your policy values, then drag the cell's fill handle down for other employees.

Use a Lookup Table for Multiple Service Awards
Use a lookup table when you have several tiers or expect the policy to change.
Calculate Service-Based Vacation Days in WPS Spreadsheet
Use a hire date and a tiered formula in WPS Spreadsheet to calculate vacation days from completed years of service.
- 1. Open your worksheet: Open WPS Spreadsheet and use Open to select your workbook, or create a new spreadsheet.
- 2. Enter hire dates: Enter an employee's valid hire date in D4 and make sure it is not in the future.
- 3. Calculate vacation days: In an empty cell, enter =IF(DATEDIF(D4,TODAY(),"y")>=18,25,IF(DATEDIF(D4,TODAY(),"y")>=12,20,IF(DATEDIF(D4,TODAY(),"y")>=5,15,10))).
- 4. Customize and copy: Change the example thresholds and day amounts to match your policy, then drag the fill handle down to calculate other employees' entitlements.

Frequently Asked Questions
Why should I check the highest service threshold first?
A nested IF or IFS formula returns the first matching result. Checking 18 years before 12 and 5 years prevents an employee with 18 years of service from receiving the five-year amount.
How can I add another vacation tier?
Add another IF condition in the formula, placing higher thresholds first. Alternatively, add a row to the lookup table in ascending threshold order and extend the VLOOKUP range to include it.
Does the formula use completed years of service?
Yes. DATEDIF(D4,TODAY(),"y") returns the number of completed years between the hire date in D4 and the current date.
Why does my formula return an error?
Check that D4 contains a valid date and that the hire date is not later than TODAY(). For a lookup formula, also check that the tier table starts at zero and its thresholds are sorted in ascending order.




