# How many employees are on leave during Easter holidays [Homework]

*Easter is around the corner*. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and *almost *forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.

**Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.**

Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. This is how your data (table name: * lvs*) looks:

** Click here to download the sample file**.

You want to answer below three questions:

- How many employees are on leave during Easter holidays (14th of April to 28th of April)?
- How many employees are on approved vacation during Easter holidays?
- How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range
**ninja**

For first question, assume that any employees whose leave is *pending *will be approved.

Also, assume that Easter season start & end dates are in cells P4 & P5 respectively.

You can use formulas, pivot tables, power pivot measures, VBA or pixie dust to solve the problem. If using pivot table approach, just explain how you would solve in words. For other methods, please post your solution in the comments.

**Go ahead and post your questions. **

**Want some hints..?**

What is an Easter themed homework without some clues? So here we go

- Between formula in Excel
- Check if your two ranges of dates overlap
- Range lookup in Excel
- Count unique values subject to conditions

All the best. The weekend forecast is blue skies and light winds. Finally, we will be checking out walking trials in Trelissick park.

### Leave a Reply

Hide columns one one tab same way as they were in another place [quick tip] |
There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs] |

## 35 Responses to “How many employees are on leave during Easter holidays [Homework]”

This is very similar to consecutive leave problem I had. I have posted a video solution here:

https://www.youtube.com/watch?v=BKa8Bbdhq7I

Further, I have also launched a Power Query course here:

https://www.udemy.com/power-query-training-for-excel-2010-2013-2016-powerbi/?ccManual=&couponCode=pqyt80

Have a look and please suggest.

1. 340=COUNTIFS(lvs[Leave Start],">="&$P$3,lvs[Leave End],"="&$P$3,lvs[Leave End],"="&$P$3,lvs[Leave End],"<="&$P$4,lvs[Status],"Approved",lvs[Emp Number],ninja)

=COUNTIFS(lvs[Leave Start],">=42839",lvs[Leave End],"<=42853")

1. Formula for answer to 1 question - COUNTIFS(lvs[Leave Start],">=14-04-17",lvs[Leave End],"<=28-04-17")+COUNTIFS(lvs[Leave Start],"=14-04-17",lvs[Leave End],"=14-04-17",lvs[Leave Start],"28-04-17")+COUNTIFS(lvs[Leave Start],"=28-04-17") = 417

2. Formula for answer to 2 question -COUNTIFS(lvs[Leave Start],">=14-04-17",lvs[Leave End],"<=28-04-17",lvs[Status],"Approved")+COUNTIFS(lvs[Leave Start],"=14-04-17",lvs[Leave End],"=14-04-17",lvs[Leave Start],"28-04-17",lvs[Status],"Approved")+COUNTIFS(lvs[Leave Start],"=28-04-17",lvs[Status],"Approved") = 293

3.Formula for answer to 3 question - COUNTIFS(lvs[Leave Start],">=14-04-17",lvs[Leave End],"<=28-04-17",lvs[Status],"Approved",lvs[Emp Number],ninja)+COUNTIFS(lvs[Leave Start],"=14-04-17",lvs[Leave End],"=14-04-17",lvs[Leave Start],"28-04-17",lvs[Status],"Approved",lvs[Emp Number],ninja)+COUNTIFS(lvs[Leave Start],"=28-04-17",lvs[Status],"Approved",lvs[Emp Number],ninja) = 2

For every answer there are 4 scenario's:

A. Leave START before 14-Apr-17 but ends Between 14-04-17 to 28-04-17.

B. Leave START after 14-Apr-17 but ends before 28-04-17.

C. Leave START between 14-Apr-17 to 28-04-17 but ends after 14-04-17 to 28-04-17.

D. Leave START before 14-Apr-17 but ends after 28-04-17.

Hence, we get above answers..

Hope this is correct thinking..

Suryakant

Everyone seems to have a different answer!

Let me walk through question 1, using just the autofilter to count numbers:

1. There are 1044 people with "Approved" Holiday

2. Of which 59 have their holiday starting after 28-04-2017. We exclude all these

3. A further 692 have their holiday ending before 14-04-2017. We exclude all these

4. 1044 - 59 - 692 = 293

5. Now repeat this process for "Pending" holidays:

6. 151 - 10 - 96 = 45

7. Add the results for approved and pending gives = 338

It's not pretty but the following formula returns that answer:

=COUNTIFS(lvs[Status],"Approved")-

COUNTIFS(lvs[Status],"Approved",lvs[Leave End],""&$P$5)+

COUNTIFS(lvs[Status],"Pending")-

COUNTIFS(lvs[Status],"Pending",lvs[Leave End],""&$P$5)

Ergh, the comments software seems to have chopped out big chunks of my formula! It doesn't seem to like working with less then and greater than symbols.

Yes, but there are duplicate Emp Numbers, you have to account for that.

For 1st = =COUNTIFS(lvs[Leave Start],">=42839",lvs[Leave End],"=42839",lvs[Leave End],"=42839",lvs[Leave End],"<=42853",lvs[Status],"Approved",lvs[Emp Number],ninja)) = 5

Dear chandoo,

Kindly specify if you want the count of leave is starting from 14/04/2017 and ending on or before 28/04/2017.

There is an ambiguity in that question.

For 1st = =COUNTIFS(lvs[Leave Start],">=42839",lvs[Leave End],"=42839",lvs[Leave End],"=42839",lvs[Leave End],"<=42853",lvs[Status],"Approved",lvs[Emp Number],ninja))

5

Dear chandoo,

Kindly specify if you want the count of leave is starting from 14/04/2017 and ending on or before 28/04/2017.

There is an ambiguity in that question.

Q1 - 408

Q2 - 286

Q3 - 5

My method:

Add two helper columns.

Helper 1 "Is Leave During Easter" : =OR(AND([@[Leave Start]]>=$Q$4,[@[Leave Start]]=$Q$4,[@[Leave End]]<=$Q$5))

Helper 2 "Is Employee on Team Ninja?" : =ISNUMBER(MATCH([@[Emp Number]],ninja,0))

Then Insert a Pivot Table, adding the Data to Data Model. Adding to Data Model allows you to get a distinct count instead of a regular count (the questions ask "How many Employees", not "How many leave requests").

Once in a pivot table, you can add filters to isolate leave requests during the holidays, with a distinct count by employee id. Then you can add the filter for Status=Approved. Finally, you can get a distinct count for those on team ninja.

Actually, I take back my response for Q2. I failed to notice that the leave Type must be vacation. The correct answer is 139.

As Josh noticed, the biggest challenge is to avoid counting duplicates, but I'll have to disagree slightly with the results of his formula/pivot work.

Assumptions:

1. Being "on leave" for Q1 means Status is not Cancelled or Declined

2. Unless explicitly stated (e.g. vacation), "leave" means any Leave Type

3. Start and end dates are inclusive (i.e. any leave that ends on April 14 or starts on April 28 is counted as an overlap)

Q1 = 331

=SUMPRODUCT(SIGN(FREQUENCY(lvs[Emp Number]*($P$4<=lvs[Leave End])*(lvs[Leave Start]<=$P$5)*ISNUMBER(MATCH(lvs[Status],{"Approved","Pending"},0)),lvs[Emp Number])))-1

Q2 = 139

=SUMPRODUCT(SIGN(FREQUENCY(lvs[Emp Number]*($P$4<=lvs[Leave End])*(lvs[Leave Start]<=$P$5)*(lvs[Status]="Approved")*(lvs[Leave Type]="Vacation"),lvs[Emp Number])))-1

Q3 = 4

=SUMPRODUCT(SIGN(FREQUENCY(lvs[Emp Number]*($P$4<=lvs[Leave End])*(lvs[Leave Start]<=$P$5)*(lvs[Status]="Approved")*ISNUMBER(MATCH(lvs[Emp Number],ninja,0)),lvs[Emp Number])))-1

1)340

2)242

3)18

3)5 is right answer

I neither used any formula nor pivot table. Solved using Advanced Filter

I can't see why the table would look like that from the beginning.

Q1) 227

Q2) 199

Q3) 5

Sorry!

New Numbers:

Q1) 267

Q2) 237

Q3) 4

q1) 338

=SUM(COUNTIFS(lvs[Leave Start],"=" &$P$4,lvs[Status],{"Approved","Pending"}))+SUM(COUNTIFS(lvs[Leave Start],">"&$P$4,lvs[Leave Start],"=" &$P$4,lvs[Leave Start], "<="&$P$5,lvs[Leave Type],"Vacation",lvs[Status],"Approved")

Hi Chandoo, To be honest, i am not really good in Excel, however, this article has given me some insight on how to use it efficiently. You will be glad to know now even I have taught a little bit to my friend. Hope to get a more post from you in the future.

Thank you

Wow, there's a lot of different numbers being proposed. Here's the way I looked at it:

I made a helper column to take out anything "Declined" or "Cancelled", then to determine if any part of the leave fell within the Easter Holiday Season range. Formula used for this part is:

'=IF(AND([@Status]"Declined",[@Status]"Cancelled"),MAX(0,MIN($P$5,[@[Leave End]])-MAX($P$4,[@[Leave Start]])+1),0)

Result is the number of days of leave that fall in the Easter Holiday Season (on leave DURING, not on leave for the ENTIRE Period).

Then, for Q1, I simply counted the values in my helper column that were greater than 0:

'=COUNTIF(lvs[helper1],">0")

Result = 338

For Q2 the question is ambiguous, since the assumption is that Pending will be approved, so the answer would be the same as Q1. However, taking it at face value, taking out the Pending and only counting the Approved, my formula is:

'=COUNTIFS(lvs[Status],"Approved",lvs[helper1],">0")

Result is 293

Haven't tackled Q3 yet.

Website hosed my Helper formula. Between [@Status] and "Cancelled" there should be GreaterThanLessThan symbols.

Sometimes I amaze myself by what I miss. For Q2, I missed "Vacation". Updated formula (NOT using a Helper Column) is:

'=COUNTIFS(lvs[Leave Start],"="&P4,lvs[Status],"Approved",lvs[Leave Type],"Vacation")

Result is: 141

Updated formula for Q1 (NOT using Helper) is:

'=COUNTIFS(lvs[Leave Start],"="&P4,lvs[Status],"Pending")+COUNTIFS(lvs[Leave Start],"="&P4,lvs[Status],"Approved")

Result is still: 338

For Q3, the answer without duplicates is 5, but I've yet to come up with a formula solution to arrive at that.

Grrr.... all the "=" signs in my last post or either Less Than or Equal, or Greater Than or Equal.

I'm tired of my formulas being hosed. LT = Less Than, GT = Greater Than:

'=COUNTIFS(lvs[Leave Start],"LT="&P5,lvs[Leave End],"GT="&P4,lvs[Status],"Approved",lvs[Leave Type],"Vacation")

'=COUNTIFS(lvs[Leave Start],"LT="&P5,lvs[Leave End],"GT="&P4,lvs[Status],"Pending")+COUNTIFS(lvs[Leave Start],"LT="&P5,lvs[Leave End],"GT="&P4,lvs[Status],"Approved")

For Q2: 141

=COUNTIFS(lvs[Leave Start], ">=" &$P$4,lvs[Leave Start], "<="&$P$5,lvs[Leave Type],"Vacation",lvs[Status],"Approved")+COUNTIFS(lvs[Leave Start],"="&$P$4,lvs[Leave Type],"Vacation",lvs[Status],"Approved")

1Q. How many employees are on leave during Easter holidays (14th of April to 28th of April)?

Ans: =COUNTIFS(lvs[[#All],[Status]],"Declined",lvs[[#All],[Status]],"Cancelled",lvs[[#All],[Leave Start]],">="&P4,lvs[[#All],[Leave Start]],"<="&P5)

2Q. How many employees are on approved vacation during Easter holidays?

Ans: =COUNTIFS(lvs[[#All],[Leave Type]],"Vacation",lvs[[#All],[Status]],"Declined",lvs[[#All],[Status]],"Cancelled",lvs[[#All],[Status]],"pENDING",lvs[[#All],[Leave Start]],">="&P4,lvs[[#All],[Leave Start]],""&P4,lvs[[#All],[Leave Start]],"<="&P5)

1 - Formula : COUNTIFS(lvs[Leave Start];">="&P4;lvs[Leave Start];"="&P4;lvs[Leave Start];"="&P4;lvs[Leave Start];"="&MIN(ninja);lvs[Emp Number];"<="&MAX(ninja);lvs[Status];"Approved") answer : 6

Madouh,

I can see you got the right answer, but I can't get your formula to work correctly. I converted the semicolons to commas, and the formula calculates, but the result is zero. Did something get left out, or mistyped, in the formula you posted?

Here is the modified and correct result:

1) How many employees are on leave during Easter holidays (14th of April to 28th of April)?

Ans: Logic used - Counted all the employees are whose leave status is either Approved or Pending and deducted : a) the count of emp whose leave start and end dates are before holiday start (14th Apr) b) the count of emp whose leave start and end dates are after holiday end (28th Apr). This gives 338 which is resultant of 1195-788-69 based on the above logic and the formula is:

=SUM(COUNTIFS(lvs[[#All],[Status]],{"Approved","Pending"}))-SUM(COUNTIFS(lvs[[#All],[Status]],{"Approved","Pending"},lvs[[#All],[Leave Start]],"<"&P4,lvs[[#All],[Leave End]],""&P5,lvs[[#All],[Leave End]],">"&P5)).

2) How many employees are on approved vacation during Easter holidays?

Ans: Applying the same logic as explained above we have to count all the employees whose leave status is approved and leave type is vacation and then deduct: "Refer answer section of 1st question".

This gives 141 which is resultant of 490-318-31 and the formula is:

=COUNTIFS(lvs[[#All],[Status]],"Approved",lvs[[#All],[Leave Type]],"Vacation")-COUNTIFS(lvs[[#All],[Status]],"Approved",lvs[[#All],[Leave Type]],"Vacation",lvs[[#All],[Leave Start]],"<"&P4,lvs[[#All],[Leave End]],""&P5,lvs[[#All],[Leave End]],">"&P5).

3) How many employees in "Team ninja" are on approved leave during Easter holidays?

Ans: Applying the same logic as explained in the above 2 questions, below is the formula.

=SUM(COUNTIFS(lvs[[#All],[Emp Number]],"="&ninja,lvs[[#All],[Status]],"Approved"))-SUM(COUNTIFS(lvs[[#All],[Emp Number]],"="&ninja,lvs[[#All],[Status]],"Approved",lvs[[#All],[Leave Start]],"<"&P4,lvs[[#All],[Leave End]],""&P5,lvs[[#All],[Leave End]],">"&P5))

Though the formula appears lengthy, this is conventional way which matches with the count when cross checked using filter criteria.

I would be happy to look easier and tricky way of writing formula from others and especially Chandoo 🙂

Q1:

FORMULA

=SUM(COUNTIF(Status,{"Approved","Pending"}))

-SUM(COUNTIFS(Status,{"Approved","Pending"},L_End,""&E_End))

ANSWER

338

=SUM(COUNTIF(Status,{"Approved","Pending"}))-SUM(COUNTIFS(Status,{"Approved","Pending"},L_End,""&E_End))

1. 267

2. 237

3. 4

267

127

2

Q1: 338

Approved or pending

Leave starts on or before 28 apr

Leave ends on or after 14 apr

=SUMPRODUCT((lvs[Leave Start]=$P$4)*ISNUMBER(MATCH(lvs[Status];{"approved";"pending"};0)))

Q2: 141

Approved & type is vacation

=SUMPRODUCT((lvs[Leave Start]=$P$4)*(lvs[Status]="approved")*(lvs[Leave Type]="vacation"))

Q3: 6

assuming type of leave doesn't matter

=SUMPRODUCT((lvs[Leave Start]=$P$4)*(lvs[Status]="approved")*ISNUMBER(MATCH(lvs[Emp Number];ninja;0)))