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

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

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

### 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:

`=DATEDIF(Database!D5,NOW(),"Y")`

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

### Step 4 – Calculate Allowed Vacation Days

Enter the **VLOOKUP** formula into the cell **E5:**

`=VLOOKUP(D5,Database!$G$6:$H$9,2)`

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

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

### Final Step – Calculate Accrued Vacation Time

Calculate the accrued vacation days with this formula:

**Accrued Vacation Days = Allowed Vacation Days – Vacation Taken**

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**

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

## 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

`=EDATE(D5,$K$5)`

**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**:

`=IFERROR(VLOOKUP(E5,Database!$H$6:$I$10,2),0)`

- 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**

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.

Thanks!

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]