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

- 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)`

### Calculate Leave Balance in Excel

To calculate the leave balance:

- After entering the Allowed Leave, input the following formula to calculate the available leave balance per leave type.

`=I6-I7`

- Drag the
**Fill Handle tool**horizontally to copy the formula to adjacent cells.

**Download Excel Workbook**

Download the free Excel workbook and practice yourself.

**<< Go Back to Formula List | Learn Excel**