How to Calculate Leave Balance in Excel (with Detailed Steps)

It is a frequent need for various companies to calculate the leave balance of their employees. Every company has a different leave structure according to employees’ years of service and company policy. In this article, I will show you how to calculate leave balance in Excel with detailed steps.


Download Sample Workbook

You can download our practice workbook from here for free.


Steps to Calculate Leave Balance in Excel

Say, you have a dataset for 10 employees with their names, joining dates, and leaves taken this year. We have to calculate their leave balance in Excel. Follow the steps below to accomplish this.

Sample dataset to Calculate Leave Balance in Excel


📌 Step 1: Create Employee Database & Leave Structure

First and foremost, you have to create the employee database & the leave structure of the company according to the years of service of the employees and the company policy.

  • To do this, create a new sheet named Database, and on the left side create two columns containing the employees’ names and joining dates.

Create Employee Database

  • Following, record the leave structure according to the company policy on the right side of the sheet.

Record Leave Structure to Calculate Leave Balance in Excel

As a result, you will have the complete database containing employees’ required information and the leave structure.

Employee Database & Leave Structure

Read More: How to Create Leave Tracker in Excel (Download Free Template)


📌 Step 2: Calculate Leave Balance for Particular Employee

Now, you have to calculate the leave balance for a particular employee on an individual sheet if you need to find the leave balance for someone definite.

  • For doing this, at first, click on the C4 cell. Subsequently, go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation… option.

Access the Data Validation Tool

  • As a result, the Data Validation dialogue box will appear. Now, choose the option List from the Allow: dropdown list. Following, at the Source: text box, refer to the B5:B14 cells of the How to Calculate Leave Balance sheet. Finally, click on the OK button.

Create Employee Dropdown List

  • At this time, you will see that, there is a down arrow on the C4 cell from where you can choose the employee’s name easily. Say, we have chosen Mathew Jones to calculate his leave balance.

Choose an Employee to Calculate Leave Balance in Excel

  • Now, click on the C7 cell and insert the following formula using the VLOOKUP function. Subsequently, press the Enter button.
=VLOOKUP($C$4,Database!$B$5:$C$14,2,FALSE)

Use VLOOKUP Function to Find the Joining Date

🔎 Formula Explanation :

=VLOOKUP($C$4,Database!$B$5:$C$14,2,FALSE)

This will look for the value of the C4 cell at the Database worksheet’s B5:C14 range and returns the second column value for the respective cell.

Result: 1/10/2009

=DATEDIF(C7,NOW(),"Y")

Use DATEDIF Function to Calculate Years of Service

🔎 Formula Explanation:

=DATEDIF(C7,NOW(),”Y”)

This calculates the difference between the C7 cell’s date and today’s date in terms of years.

Result: 13

  • Following, click on the C9 cell and insert the following formula. Next, press the Enter button.
 =VLOOKUP($C$4,'How to Calculate Leave Balance'!$B$5:$D$14,3,FALSE)

Extract the Leaves Taken Value from the Dataset

🔎 Formula Explanation:

=VLOOKUP($C$4,’How to Calculate Leave Balance’!$B$5:$D$14,3,FALSE)

This returns the third column value from the How to Calculate Leave Balance sheet’s B5:D14 range for the lookup value of the C4 cell of this worksheet.

Result: 7

  • At this time, to calculate the leaves allowed, click on the C10 cell. Following, insert the formula below using the IF function. Afterward, press the Enter button.
=IF(C8>20,25,IF(C8>15,20,IF(C8>10,16,IF(C8>5,12,IF(C8>1,8,2)))))

Use the IF Function to Calculate Leaves Allowed

🔎 Formula Explanation:

=IF(C8>20,25,IF(C8>15,20,IF(C8>10,16,IF(C8>5,12,IF(C8>1,8,2)))))

This checks if the C8 cell’s value is greater than 20. If it is true, it returns the value 25. When it is not true, it checks if the value is greater than 15. If it is true now, it returns 20. When it is false again, now it checks if the value is greater than 10. At this time, if it is true now, it returns the value as 16. But, If it is false too, it checks if the value is greater than 5. It returns 12 then. But, if it is false again, it checks if the value is greater than 1. It returns 8 then. But when it is false too, it would return 2 as the C10 cell’s value.

Result: 16

  • Last but not least, to calculate the leaves remaining, click on the C11 cell. Subsequently, write the following formula which involves the subtraction of the C9 cell from the C10 cell.
=C10-C9

Calculate Leave Balance in Excel

Thus, you have calculated the leave balance for a particular employee. You can calculate and print anyone’s leave balance individually by this. For example, the outcome should look like this.

Calculated Leave Balance of a Particular Employee

Read More: Employee Leave Record Format in Excel (Create with Detailed Steps)


📌 Step 3: Calculate Leave Balance for All Employees

Now, after the individual leave balance calculation, you need to calculate al employees’ leave balance together in a signal table for better visualization.

  • To accomplish this, at the very beginning, click on the D5 cell. Next, insert the following formula. Subsequently, press the Enter button.
=DATEDIF(C5,NOW(),"Y")

Use DATEDIF Function to Calculate the Years of Service

🔎 Formula Explanation:

=DATEDIF(C5,NOW(),”Y”)

This calculates the year difference between today and the C5 cell’s date.

Result: 13

  • At this time, you have calculated the years of service for Mathew Jones. To apply a similar formula for others, place your cursor on the bottom right position of the cell and drag it downward.

Drag Fill Handle to Calculate the Years of Service for All

  • Afterward, click on the E5 cell and refer to the How to Calculate Leave Balance worksheet’s D5 cell.

Refer to the Leaves Taken Value

  • At this time, place your cursor in the bottom right position of your cell. As a result, when the black fill handle appears, drag it down to repeat the same formula for all the other cells below.

Extract All Leaves Taken Values Using Fill Handle

  • Afterward, click on the F5 cell and write the following formula. Subsequently, press the Enter button.
=IF(D5>20,25,IF(D5>15,20,IF(D5>10,16,IF(D5>5,12,IF(D5>1,8,2)))))

Use the IF Function to Calculate the Leaves Allowed value

🔎 Formula Explanation:

=IF(D5>20,25,IF(D5>15,20,IF(D5>10,16,IF(D5>5,12,IF(D5>1,8,2)))))

It checks if the D5 cell is greater than 20 or 15 or 10 or 5 or 1. When greater than 20, it returns 25. If greater than 15, it returns 20. At the condition of greater than 10, it returns 16. When greater than 5, it returns 12.  And, if greater than 1, it returns 8. Else, it returns 2.

Result: 16

  • Next, place your cursor in the bottom right position of your cell. Subsequently, drag the fill handle downward upon its arrival.

Use Fill Handle to Copy Same Formula

  • Last but not the least, click on the G5 cell and subtract the E5 cell from the F5 cell. So write the following formula and press the Enter button.
=F5-E5

Calculate Leave Balance in Excel

  • Afterward, place your cursor on the bottom right position of the cell and drag the fill handle downward when it appears to copy the same formula below.

Use Fill Handle to Calculate All Employees' Leave Balance

Finally, you can calculate all your employees’ leave balances together in a single table. And, the final outcome should look like this.

Final Result of Calculated Leave Balance in Excel

Read More: Employee Monthly Leave Record Format in Excel (with Free Template)


Conclusion

To conclude, in this article, I have shown you detailed steps on how to calculate leave balance in Excel. I would suggest you read the full article and practice from our workbook parallel to have a strong grip on the topic. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And visit ExcelDemy to find many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo