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.


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.

Excel Attendance Sheet Information

Now, the employees have taken both the full day leaves and half day leaves.

Excel Attendance Sheet

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

Use COUNTIFS Function to Calculate Half Day in Excel Attendance Sheet

🔎 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.

Drag the Fill Handle Downward to Copy Same Formula

Thus, you can calculate the total leaves of all the employees. And, for example, the result would look like this.

Total Leaves Including the Half Days

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)

Use the SUMPRODUCT Function to Calculate Half Days in Excel Attendance Sheet

  • 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.

Drag the Fill Handle Below to Copy Same Formula

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.

Total Leaves Including Half Days

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

Combine the SUM and COUNTIF Function to Calculate Half Day in Excel Attendance Sheet

🔎 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.

Drag Fill Handle Below to Copy Same Formula

Consequently, you will get the total leaves calculated including the half days for each employee. For instance, the outcome should look like this.

Total Leaves with Half Days

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!


Related Articles

Tanjim Reza

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo