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.
Attendance Sheet in Excel with Formula for Half Day: 3 Examples
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 created an attendance sheet with formula, follow the instructions below.
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: How to Create a Monthly Staff Attendance Sheet in Excel
3. Combining SUM and COUNTIFS Functions
Moreover, you can combine the SUM and COUNTIFS functions 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
Download Practice Workbook
You can download our practice workbook from here for free!
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.
Related Articles
- How to Create Training Attendance Sheet in Excel
- Labour Attendance Sheet Format in Excel
- How to Create Biometric Attendance Report in Excel
- How to Prepare a Meeting Attendance Sheet in Excel
- How to Create Attendance Sheet with Time in and Out in Excel
- How to Create Monthly Attendance Sheet in Excel with Formula
<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
