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.


How to Calculate Half Day Leave in Excel: 2 Ways

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.


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.

  • Next, use the Fill Handle to AutoFill the lower cells.

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: How to Calculate Annual Leave in Excel


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


Download Practice Workbook


Conclusion

In a nutshell, you will get a basic idea of how to calculate half day leave in Excel after reading this article. If you have any better methods, questions or feedback regarding this article, please share them in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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