# Attendance Sheet in Excel with Formula for Half Day (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 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. ### 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. ## Conclusion

And, visit ExcelDemy for many more articles like this. Keep learning and keep growing!

## Related Articles #### Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  