How to Calculate Half Day Leave in Excel (2 Effective Methods)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to calculate half day leave in Excel. When you are a manager in a company or an organization, you need to monitor the leave statuses of your employees. Leave can be classified into two types: Full Day and Half Day Leave. If an employee works half of his working hours in a day and then takes a leave, that working day should be counted as half. In this article, I’ll show you how to calculate both half day leave and half working days in Excel.


Download Practice Workbook


2 Ways to Calculate Half Day Leave in Excel

In the dataset, we have the attendance statuses of an employee in a period.

how to calculate half day leave in excel


1. Using Excel COUNTIF Function to Calculate Half Day Leave

We can easily calculate the half day leaves of an employee using the COUNTIF function. Let’s go through the process below for a better understanding.

Steps:

  • First, select a cell to store the number of half day leaves and type the following formula in it.

=SUM(COUNTIF(C5:C10,{"Present","Full Day Leave","Half Day Leave"})*{0,0,0.5})

Here, we are using an array formula to count both “Present” and “Full Day Leave” as 0 (zeros) and “Half Day Leave” as 0.5. The COUNTIF function will count the number of each category ( “Present”, ”Full Day Leave”, and “Half Day Leave”) and will return an array containing the number of the types. This will be multiplied by the {0,0,0.5} array. The SUM function then sums up all the half-day leaves and returns that value.

  • After that, press the ENTER button and you will see the number of half-day leaves in cell C15.

how to calculate half day leave in excel

Finally, we get the calculated result of half day leave by using Excel COUNTIF and SUM functions.

Read More: How to Create Leave Tracker in Excel (Download Free Template)


2. Implementing Combined Formula to Calculate Half Day Leave

In this section, I’ll show you how to calculate half day working days in a given period using combined functions. It will help you to differentiate between full and half day working days. Because, you cannot mix them up with full working days. Suppose, in an organization, employees work half of the day on Friday. So, we should count Friday as a half working day. Let’s follow the instructions below for a better perspective.

Steps:

  • First, make a column to store half days of working and type the following formula in it.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B5&":"&C5)))=6))/2

The formula uses SUMPRODUCT, WEEKDAY, ROW and INDIRECT functions.

Formula Breakdown

  • INDIRECT(B5&”:”&C5) —-> returns an array that has a row number the same as the date in cell C5 converted to its number
  • ROW(INDIRECT(B5&”:”&C5)) —-> turns into an array of dates (in number format) from the 4th February to the 23rd February.
    • Output: {44596;44597;44598;44599;44600;44601;44602;44603;44604;44605;44606;44607;44608;44609;44610;44611;44612;44613;44614;44615}
  • WEEKDAY(ROW(INDIRECT(B5&”:”&C5))) —-> returns an array of weekdays in number from the 4th February to the 23rd February.
    • Output: {6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4}
  • –(WEEKDAY(ROW(INDIRECT(B5&”:”&C5)))=6) —-> will become the following array where the 6th day of the week will be noted as 1 and others as 0.
    • Output: {1;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0}
  • SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(B5&”:”&C5)))=6))/2 —-> turns into the summation of half days.
    • Output: 1.5
  • After that, hit the ENTER button and you will see the number of half working days in cell D5 from the 4th February to the 23rd February.

how to calculate half day leave in excel

Thus you can calculate half day leave or half working days in Excel by using combined functions.

Read More: Employee Leave Record Format in Excel (Create with Detailed Steps)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

how to calculate half day leave in excel


Conclusion

In a nutshell, you will get the basic idea of how to calculate half day leave in Excel after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Meraz Al Nahian

Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo