Leave Calculation Formula in Excel (5 Types of Leave)

The Leave Calculation Formula in Excel is essential for accurately calculating and tracking employee leave balances. In this Excel tutorial, you will learn to perform the leave calculation formula in Excel.

It helps automate the process of calculating accrued leaves, taken leaves, and remaining balances. This not only saves time but also mitigates the chances of errors that may occur with manual calculations. The formula ensures consistency and accuracy in managing employee leave records, facilitating efficient human resource management and payroll processing.

Consider a sample dataset containing working hours and leave types of an employee. We calculate half-day, casual, and sick leave for each month.

Calculate Half Day Leave in Excel

To calculate half-day leave in Excel, you must count the working hours first. Then the combination of the IF, HOUR, and MINUTES functions will be helpful to mark the half-day leave. In this scenario, if the working hour is less than 6 hours, we will label it as half-day leave.

To calculate half-day leave in Excel, follow the steps:

Insert the formula to calculate working hours:
=IFERROR(D6-C6,"-")
Use the Fill Handle to copy the formula in the adjacent cells.
Thus, we obtain the working hours with a by-default format.
Next, Select the range where you want to apply formatting and press the Ctrl + 1 keys to open the Format Cells dialog box.
Now, select the Number tab > Custom > hh:mm > OK.
As a result, we get the time format in hh:mm format.
To obtain the half-day leave, Input the following formula and use the Fill Handle tool.
=IFERROR(IF((HOUR(E6) +( MINUTE(E6)/ 60))<6,"Half Day Leave","-"),"-")
Once the working hour is less than 6 hours, the formula returns “Half Day Leave”.
Finally, to calculate the total half-day leave insert the following formula:
=COUNTIF(F6:F34,"Half Day Leave")
As a result, we obtain a total of 4 days of half-day leave.

Calculate Monthly Leave in Excel

Now we have a worksheet containing all leaves. First, we will count all the leave separately and then calculate the total leave.

To calculate the monthly leave in Excel:

Insert the following formula for calculating leave separately:
=COUNTIF($F$6:$F$34,H5)
Drag down the Fill Handle tool to copy the formula to the adjacent cells.
Here, we obtain Half-day, medical, and casual leaves with the formula and Fill Handle tool.
Finally, use the following formula to calculate the total leave in a month.
=(I5/2)+I6+I7
As you can see, we calculated a total of 4 days of monthly leave.

Calculate Annual Leave in Excel

If you have year-long data, you can easily calculate annual leave in Excel. Assume, we have a year-long detailed data. We will calculate Half-day, Casual, and Sick leave separately for each month by combining COUNT, FILTER, MONTH, and ROWS functions. Then the total leave will be counted. Here’s how:

Use the following formula to calculate the half-day leave.
=COUNT(FILTER($B$6:$B$370, ($F$6:$F$370=I$5)*(MONTH($B$6:$B$370)=ROWS($H$6:$H6))))
The MONTH function returns the month number. The ROWS number also returns the number of numbers. Matching the number of months and leave type, we obtain an array result with the FILTER function. Then the COUNT function counts the instances.
Then use the Fill Handle tool horizontally and vertically to calculate sick and casual leave as well for each month.
Then use the following formula to calculate the total half-day leave.
=SUM(I6:I17)
Use the Fill Handle tool horizontally again to get the total sick and casual leave.
Finally, apply the following formula to calculate total leave in a calendar year.
=SUM(L6:L17)
Here, we obtain 28.5 days of total leave in a calendar year.

Calculate Accrued Vacation Leave in Excel

Suppose, your company allows you 1 day of accrued vacation leave after completing every 21 days of office days. So, we will calculate the performing office days first and calculate the accrued vacation time in Excel by dividing 21.

For calculating accrued vacation leave in Excel:

Insert the following formula to calculate the total number of office days.
=(SUMPRODUCT(--ISNUMBER(E6:E370)))
Then we calculate the accrued vacation leave by applying the following formula.
=ROUNDDOWN((SUMPRODUCT(--ISNUMBER(E6:E370)))/21,0)

Calculate Leave Balance in Excel

If you want to calculate a leave balance in Excel, you must know the allowable sick, casual, and accrued leaves. By deducting the taken leaves, you will obtain the available leaves.

To calculate the leave balance in Excel:

Use the following formula and drag right the Fill Handle tool.
=I6-I7
As a result, we obtain all the remaining leaves.

Download Excel Workbook

Download the free Excel workbook and practice yourself.

Leave Calculation Formula.xlsx

Conclusion

In short, we explained the leave calculation formula in Excel. You can calculate half-day, casual, and sick leaves for each month. If leaves of year-long data are available, then annual leave, accrued leave, and leave balance, can be calculated. Feel free to share your insight, suggestions, and queries in the comment section below.