The article will show you how to calculate half day leave in Excel. When you are a manager in a company or an organization, you need to monitor the leave statuses of your employees. Leave can be classified into two types: full day and half day Leave. If an employee works half of his working hours in a day and then takes a leave, that working day should be counted as half. In this article, I’ll show you how to calculate both half day leave and half working days in Excel.
How to Calculate Half Day Leave in Excel: 2 Ways
In the dataset, we have the attendance statuses of an employee in a period.
1. Using Excel COUNTIF Function to Calculate Half Day Leave
We can easily calculate the half day leaves of an employee using the COUNTIF function. Let’s go through the process below for a better understanding.
Steps:
- First, select a cell to store the number of half day leaves and type the following formula in it.
=SUM(COUNTIF(C5:C10,{"Present","Full Day Leave","Half Day Leave"})*{0,0,0.5})
Here, we are using an array formula to count both “Present” and “Full Day Leave” as 0 (zeros) and “Half Day Leave” as 0.5. The COUNTIF function will count the number of each category ( “Present”, ”Full Day Leave”, and “Half Day Leave”) and will return an array containing the number of the types. This will be multiplied by the {0,0,0.5} array. The SUM function then sums up all the half-day leaves and returns that value.
- After that, press the ENTER button and you will see the number of half-day leaves in cell C15.
Finally, we get the calculated result of half day leave by using Excel COUNTIF and SUM functions.
2. Implementing Combined Formula to Calculate Half Day Leave
In this section, I’ll show you how to calculate half day working days in a given period using combined functions. It will help you to differentiate between full and half day working days. Because, you cannot mix them up with full working days. Suppose, in an organization, employees work half of the day on Friday. So, we should count Friday as a half-working day. Let’s follow the instructions below for a better perspective.
Steps:
- First, make a column to store half days of working and type the following formula in it.
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B5&":"&C5)))=6))/2
The formula uses SUMPRODUCT, WEEKDAY, ROW and INDIRECT functions.
Formula Breakdown
- INDIRECT(B5&”:”&C5) —-> returns an array that has a row number the same as the date in cell C5 converted to its number
- ROW(INDIRECT(B5&”:”&C5)) —-> turns into an array of dates (in number format) from the 4th February to the 23rd February.
- Output: {44596;44597;44598;44599;44600;44601;44602;44603;44604;44605;44606;44607;44608;44609;44610;44611;44612;44613;44614;44615}
- WEEKDAY(ROW(INDIRECT(B5&”:”&C5))) —-> returns an array of weekdays in number from the 4th February to the 23rd February.
- Output: {6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4}
- –(WEEKDAY(ROW(INDIRECT(B5&”:”&C5)))=6) —-> will become the following array where the 6th day of the week will be noted as 1 and others as 0.
- Output: {1;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0}
- SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(B5&”:”&C5)))=6))/2 —-> turns into the summation of half days.
- Output: 1.5
- After that, hit the ENTER button and you will see the number of half working days in cell D5 from the 4th February to the 23rd February.
- Next, use the Fill Handle to AutoFill the lower cells.
Thus you can calculate half day leave or half working days in Excel by using combined functions.
Read More: How to Calculate Annual Leave in Excel
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In a nutshell, you will get a basic idea of how to calculate half day leave in Excel after reading this article. If you have any better methods, questions or feedback regarding this article, please share them in the comment box.
