Calculation Issues

Calculate Vacation Days from Years of Service in Excel

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

Calculate an employee's vacation entitlement from their hire date and completed years of service.

Calculate Vacation Days from Years of Service in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the hire date

Type the employee's hire date in cell D4 and format it as a date using Home > Number Format.

2
Enter the entitlement formula

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

3
Apply your policy and fill down

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 Nested IF Formula for Vacation Tiers
Completed years: DATEDIF calculates completed years rather than approximating each year as 365 days. TODAY() uses the current date when the workbook recalculates.
Calculate vacation entitlement

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. 1. Open your worksheet: Open WPS Spreadsheet and use Open to select your workbook, or create a new spreadsheet.
  2. 2. Enter hire dates: Enter an employee's valid hire date in D4 and make sure it is not in the future.
  3. 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. 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.
Calculate vacation tiers using completed service yearsOpen and edit Microsoft Excel XLSX filesFill formulas down for multiple employeesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.