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.


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.

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

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


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


<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo