How to Calculate Accrued Vacation Time in Excel (with Easy Steps)

What Is Accrued Vacation Time?

Generally, employees get a certain amount of days to leave for vacation, personal reasons, or sickness. But if the employee hasn’t used the earned leave days then this will be called Accrued Vacation Time. And the employee will earn the amount equivalent to the accrued vacation time at the end of the year. This is also called PTO – Paid Time Off.

The sample dataset has details for a number of employees including their wage, years of service, and vacations taken.

How to Calculate Accrued Vacation Time in Excel

Step 1 – Create Paid Time Off (PTO) Structure

  • Create a table with the relevant information regarding paid leave days for employees based on their years of service.
  • Create one more column that  will be used for the VLOOKUP formula.

Create Paid Time Off (PTO) Structure

Step 2 – Create Employee Database with Joining Dates

Collect the data of employee names, joining dates, and wages and make a table with this data in an Excel worksheet.

Create Employee Database with Joining Date

Step 3 – Calculate Years of Service

Use the DATEDIF function to calculate the years from the joining date until today. Enter this formula into cell D5:


Calculate Years of Service

  • Drag the Fill Handle icon to paste the formula to the other cells in the column.

Calculate Years of Service

Step 4 – Calculate Allowed Vacation Days

Enter the VLOOKUP formula into the cell E5:


Formula Explanation

  • Lookup_value = D5
    The function will look for the value in cell D5 in the lookup table.
  • Table_array = Database!$G$6:$H$9
    The range of cells that the function will search for the lookup value.
  • Col_index_num = 2
    It will return the value of column 2 in the lookup table from the row where the lookup value exists.

Calculate Allowed Vacation Days

Step 5 – Insert the Number of Vacation Days Taken from Employees’ Attendance Tracker

Open the Employee attendance tracker for that year and link the cell containing the total leave days in cell F5 or insert the values manually in the vacation taken column.

Insert the Number of Vacation Days Taken from Employees Attendance Tracker

Final Step – Calculate Accrued Vacation Time

Calculate the accrued vacation days with this formula:

Accrued Vacation Days = Allowed Vacation Days – Vacation Taken 

Calculate Accrued Vacation

You can calculate the accrual vacation payment to encash the leave days not taken. Enter the formula:

Accrued Vacation Payment = Accrued Vacation Days x Daily Wages

Calculate Accrued Vacation Time 

The accrued vacation days and the accrued vacation payments of the employees of the company have been calculated.

How to Calculate Accrued Vacation Time in Excel

Calculate Accrued Vacation Time in Excel Excluding Probationary Period

  • Insert a new column to calculate the date the probationary period ended (or passed). Select a cell to record the length of the probationary period in months.

  • Insert the following formula into cell E5 and drag the fill handle to the last row of the table
The EDATE function gives the date value after adding a given number of months. Here, It is adding 6 months which is the probationary period with the joining date, and returns the passing date.

  • Go to the Accrued Vacation worksheet and rename column D as Months of Service.  Add a new column after that named Years of Service.
  • Insert the following formula into the cell D5:
=IFERROR(DATEDIF(Database!E5,NOW(),"M"),"In Probation")
The IFERROR function returns “In Probation” if the end date in the DATEDIF function is after the present date. And, for these rows, you don’t have to calculate the accrued vacation time.

  • Calculate the years of service using the following formula in cell E5 and drag the fill handle to apply similar formula for other cells also.
=IFERROR(D5/12,"In Probation")

  • Insert the following formula to get the allowed accrued vacation time for the employees in cell F5:

  • Calculate the remaining part using the Final Step of the previous method.

Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to Leave Calculation | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

  1. I have a question.. How can you figure in a 60 day probationary period? Our employees don’t start accruing time until they’ve completed a 60 day probation.


    • Reply Avatar photo
      Osman Goni Ridwan Oct 3, 2022 at 8:39 AM

      Hello BRYAN!
      As per your question, I have added a new portion in the article which contains the method how you can calculate the accrued time after the completion of probationary period automatically.

      I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

Leave a reply

Advanced Excel Exercises with Solutions PDF