Leave Calculation Formula in Excel (5 Leave Types)

Last updated: May 16, 2024

Say you have a dataset containing working hours and leave types of an employee. You want to calculate half-day, casual, and sick leave for each month.

Calculate Half Day Leave in Excel

To calculate half-day leave:

Insert the following formula to calculate working hours:
=IFERROR(D6-C6,"-")
Use the Fill Handle tool to copy the formula to adjacent cells.
Select the range where you want to apply formatting and press the Ctrl + 1 keys to open the Format Cells dialog box.
Select the Number tab > Custom > hh:mm > OK.
The time is now in hh:mm format.
To obtain the half-day leave, input the following formula and use the Fill Handle tool to copy the formula to adjacent cells.
=IFERROR(IF((HOUR(E6) +( MINUTE(E6)/ 60))<6,"Half Day Leave","-"),"-")
If the working hour total is less than 6 hours, the formula returns “Half Day Leave”.
To calculate the total number of half-day leaves insert the following formula:
=COUNTIF(F6:F34,"Half Day Leave")

Calculate Monthly Leave in Excel

To calculate monthly leave:

Input the following formula for calculating leave separately:
=COUNTIF($F$6:$F$34,H5)
Drag down the Fill Handle tool to copy the formula to adjacent cells.
The following formula will calculate the total number of leaves in a month.
=(I5/2)+I6+I7

Calculate Annual Leave in Excel

To calculate annual leave:

Use the following formula to calculate the various types of leave per month for the year.
=COUNT(FILTER($B$6:$B$370, ($F$6:$F$370=I$5)*(MONTH($B$6:$B$370)=ROWS($H$6:$H6))))
Then use the Fill Handle tool both horizontally and vertically to calculate sick and casual leave for each month.
Use the following formula to calculate the total half-day leave.
=SUM(I6:I17)
Use the Fill Handle tool horizontally to get the total sick and casual leave, as well.
To calculate total leave for the year, use the following formula.
=SUM(L6:L17)

Calculate Accrued Vacation Leave in Excel

Suppose your company allows 1 day of accrued vacation leave per 21 work days.

To calculate accrued vacation leave:

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