Function Problems

How to Calculate Vacation Days by Hire Date in Excel

Ayan Masood Oct 10, 2026 869 views

Question details

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

How to Calculate Vacation Days by Hire Date in Excel

Product: Excel
Device & OS: not provided
Scenario: A manager or HR professional needs to automatically calculate annual leave entitlement for employees by measuring their exact tenure from their initial hire date.
Observed behavior: The user wants a formula that measures the completed years of service and assigns specific vacation day thresholds (e.g., 5 days for 1 year, 10 days for 5 years, etc.).

Before you start

Ensure that the column containing employee hire dates is properly formatted as 'Date' in your spreadsheet, rather than as plain text.

Solution 1Recommended

Use a combined formula with LET, DATEDIF, and IFS

Combine the DATEDIF function to calculate exact years of service with the LET and IFS functions to cleanly assign the appropriate vacation days based on tenure.

This method is highly efficient for modern spreadsheet versions. The LET function allows you to calculate the years of service once and assign it to a variable, while the IFS function evaluates multiple tenure conditions sequentially without requiring messy nested statements.

Select the target cell

Click on the cell where you want the calculated vacation days to appear (for example, cell B5).

Enter the primary formula

Type the following formula: =LET(Y,DATEDIF(A5,TODAY(),"Y"),IFS(Y=0,0,Y<5,5,Y<10,10,TRUE,15)). This assumes that cell A5 contains the employee's hire date.

Apply and fill down the column

Press Enter to see the result. Then, click and drag the small square fill handle at the bottom-right corner of the cell down the column to apply the calculation to the rest of the employee list.

Use a combined formula with LET, DATEDIF, and IFS

Understanding the formula output: This formula automatically returns 0 days for less than one year of service, 5 days for 1 to 4 years, 10 days for 5 to 9 years, and 15 days for 10 or more years of service.

Solution 2

Use nested IF and DATEDIF functions (For older versions)

If you are using an older version of Excel that does not support the LET or IFS functions, use a traditional nested IF formula.

Smart HR Management

Easily Calculate Vacation Entitlements in WPS Spreadsheet

WPS Spreadsheet fully supports advanced HR formulas like DATEDIF, IFS, and LET, making employee leave tracking fast, highly efficient, and perfectly accurate.

1. Download WPS Office: Download WPS Office for free from the official website and complete the quick installation process.
2. Open your HR roster: Launch WPS Spreadsheet and open your existing employee data file.
3. Apply the calculation: Insert the DATEDIF vacation calculation formula into the target column to instantly determine employee entitlements.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in support for advanced functions like DATEDIF, LET, and IFS.Completely free, lightweight, and features a familiar user interface.Seamlessly migrate your existing HR trackers without breaking formulas.

microsoft office alternative - wps office

Frequently Asked Questions

What does the DATEDIF function do in this formula?

The DATEDIF function calculates the difference between two dates. By utilizing the "Y" argument, it specifically counts the number of full, completed years between the employee's hire date and today's date.

Can I customize the vacation day thresholds in the formula?

Yes, you can easily alter the numbers within the IFS function to match your company's policy. For example, if employees receive 7 days after 3 years, you would change Y<5,5 to Y<3,7.

Why is my formula returning a #NAME? error?

A #NAME? error typically occurs if your spreadsheet software version is too old to support the newer LET or IFS functions. If you encounter this, try using the alternative nested IF formula provided in the second solution.

How do I calculate vacation days based on months instead of years?

If your vacation accrual is based on completed months, change the "Y" parameter in the DATEDIF function to "M". You will then need to adjust your IFS thresholds to check for month milestones (e.g., Y<6 for 6 months).

Excel & Data Problems