Tanjim Reza
An attendance sheet is very important and handy for offices, schools, and other institutions. We can make an attendance sheet dynamically very quickly with Excel. When working with attendance sheets, sometimes we need to record and calculate half-day leaves. If you are also looking for ways to do this, you have landed in the perfect place. Here, I will show you 3 examples to get an attendance sheet in Excel with a formula for half day.
Download Practice Workbook
You can download our practice workbook from here for free!
3 Examples with Excel Formula to Calculate Half Day in Attendance Sheet
Say, you have an attendance sheet of 10 workers for the month of January in 2022. You have a record of the holidays of the given month and the leaves taken by each employee. To know, how we have generated this attendance sheet, click here.
Now, the employees have taken both the full day leaves and half day leaves.
Now, you can calculate the proper count of leaves including the half-day counts using the 3 formulas given below.
We have used the Office 365 version of Microsoft Office here. But, you can use these formulas in any other version that is available to you. If you face any problems regarding this, please leave a comment below.
1. Using COUNTIFS Function
The most used formula to calculate half day in the Excel attendance sheet is the COUNTIFS function. Follow the steps below to use this function to get your desired result.
📌 Steps:
- First and foremost, make a new column for each employee to calculate their total leaves.
- Afterward, click on the C5 cell and insert the following formula.
=COUNTIFS('Attendance Sheet'!C8:AG8,"PL")+COUNTIFS('Attendance Sheet'!C8:AG8,"A")+COUNTIFS('Attendance Sheet'!C8:AG8,"HL")/2
🔎 Formula Breakdown:
=COUNTIFS(‘Attendance Sheet’!C8:AG8,”PL”)
This returns you the result of how many “PL” values are there in the range of C8:AG8 cells in the Attendance Sheet worksheet.
Result: 0
COUNTIFS(‘Attendance Sheet’!C8:AG8,”A”)
This returns you the result of how many “A” values are there in the range of C8:AG8 cells in the Attendance Sheet worksheet.
Result: 0
COUNTIFS(‘Attendance Sheet’!C8:AG8,”HL”)/2
This returns you the result of how many “HL” values are there in the range of C8:AG8 cells in the Attendance Sheet worksheet. And, divide the result by 2.
Result: 0
=COUNTIFS(‘Attendance Sheet’!C8:AG8,”PL”)+COUNTIFS(‘Attendance Sheet’!C8:AG8,”A”)+COUNTIFS(‘Attendance Sheet’!C8:AG8,”HL”)/2
This sums up all the previous breakdown results.
Result: 0
- Subsequently, hit the Enter button.
- As a result, you can get the total leaves of the first employee, John.
- Now, place your cursor in the bottom right position. Following, drag the fill handle downward when it appears.
Thus, you can calculate the total leaves of all the employees. And, for example, the result would look like this.
Read More: How to Create Employee Attendance Sheet with Time in Excel
2. Using SUMPRODUCT Function
Another formula you can use to calculate the half day of attendance sheet in Excel is the SUMPRODUCT function. Go through the steps below to accomplish your desired result with this function.
📌 Steps:
- At the very beginning, click on the C5 cell.
- Subsequently, write the following formula in the formula bar.
=SUMPRODUCT(('Attendance Sheet'!C8:AG8="PL")+('Attendance Sheet'!C8:AG8="A")+('Attendance Sheet'!C8:AG8="HL")/2)
- Following, press the Enter button.
- Consequently, you will get the total leaves taken by John.
- Now, place your cursor in the bottom right position of the cell.
- As a result, a black fill handle will appear. Now, drag it below to copy the same formula to all the other cells below.
As a result, you will be able to calculate the total leaves including all the half days of each employee. And, the output would look like this.
Read More: Attendance Sheet with Salary in Excel Format (with Easy Steps)
3. Combining SUM and COUNTIFS Functions
Moreover, you can combine the SUM function and the COUNTIFS function to achieve your desired results.
📌 Steps:
- Initially, click on the C5 cell and insert the following formula.
=SUM(COUNTIFS('Attendance Sheet'!C8:AG8,{"PL","A","HL"})*{1,1,0.5})
🔎 Formula Breakdown:
COUNTIFS(‘Attendance Sheet’!C8:AG8,{“PL”,”A”,”HL”})*{1,1,0.5}
This function finds the “PL”, “A”, and “HL” values in the range of C8:AG8 cells of the Attendance Sheet. Afterward, they multiply the results by 1, 1, and 0.5 respectively.
Result:0,0,0
=SUM(COUNTIFS(‘Attendance Sheet’!C8:AG8,{“PL”,”A”,”HL”})*{1,1,0.5})
This sums up the results got by the COUNTIFS function.
Result: 0
- Subsequently, hit the Enter button.
- Afterward, place your cursor in the bottom right position of the cell.
- As a result, a black fill handle will appear. Drag it downward to copy the same formula for all the cells below.
Consequently, you will get the total leaves calculated including the half days for each employee. For instance, the outcome should look like this.
Read More: Attendance and Overtime Calculation Sheet in Excel
Conclusion
To conclude, in this article, I have shown you 3 applicable formulas to calculate half day in an Excel attendance sheet. I would suggest you go through the full article carefully and practice thoroughly. You can also download our practice workbook from here for free. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy for many more articles like this. Keep learning and keep growing!
