Leave Calculation Formula in Excel (5 Types of Leave)

Get FREE Advanced Excel Exercises with Solutions!

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.
Overview of leave calculation formula in Excel


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:

  1. Insert the formula to calculate working hours:
    =IFERROR(D6-C6,"-")
  2. Use the Fill Handle to copy the formula in the adjacent cells.
    Thus, we obtain the working hours with a by-default format.
    Calculating working hour
  3. Next, Select the range where you want to apply formatting and press the Ctrl + 1 keys to open the Format Cells dialog box.
  4. Now, select the Number tab > Custom > hh:mm > OK.
    Selecting custom time formatAs a result, we get the time format in hh:mm format.
    Applying custom time format
  5. 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”.
    Finding half day leaves
  6. 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.
    Calculation of half day leave with Excel COUNTIF function.

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:

  1. Insert the following formula for calculating leave separately:
    =COUNTIF($F$6:$F$34,H5)
  2. 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.
    Leave calculation formula in Excel using COUNTIF function
  3. 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.
    Calculating total monthly leave in Excel

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:

  1. 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.
  2. Then use the Fill Handle tool horizontally and vertically to calculate sick and casual leave as well for each month.
    Calculating leave for each months
  3. Then use the following formula to calculate the total half-day leave.
    =SUM(I6:I17)
  4. Use the Fill Handle tool horizontally again to get the total sick and casual leave.
    Calculating total leave
  5. 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.
    Calculation of annual leave with Excel formula

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:

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

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.
    Calculating leave balance

Download Excel Workbook

Download the free Excel workbook and practice yourself.


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.


<< Go Back to Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo