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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.

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/powerquerytrainingforexcel201020132016powerbi/?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],">=140417",lvs[Leave End],"<=280417")+COUNTIFS(lvs[Leave Start],"=140417",lvs[Leave End],"=140417",lvs[Leave Start],"280417")+COUNTIFS(lvs[Leave Start],"=280417") = 417
2. Formula for answer to 2 question COUNTIFS(lvs[Leave Start],">=140417",lvs[Leave End],"<=280417",lvs[Status],"Approved")+COUNTIFS(lvs[Leave Start],"=140417",lvs[Leave End],"=140417",lvs[Leave Start],"280417",lvs[Status],"Approved")+COUNTIFS(lvs[Leave Start],"=280417",lvs[Status],"Approved") = 293
3.Formula for answer to 3 question  COUNTIFS(lvs[Leave Start],">=140417",lvs[Leave End],"<=280417",lvs[Status],"Approved",lvs[Emp Number],ninja)+COUNTIFS(lvs[Leave Start],"=140417",lvs[Leave End],"=140417",lvs[Leave Start],"280417",lvs[Status],"Approved",lvs[Emp Number],ninja)+COUNTIFS(lvs[Leave Start],"=280417",lvs[Status],"Approved",lvs[Emp Number],ninja) = 2
For every answer there are 4 scenario's:
A. Leave START before 14Apr17 but ends Between 140417 to 280417.
B. Leave START after 14Apr17 but ends before 280417.
C. Leave START between 14Apr17 to 280417 but ends after 140417 to 280417.
D. Leave START before 14Apr17 but ends after 280417.
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 28042017. We exclude all these
3. A further 692 have their holiday ending before 14042017. 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 119578869 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 49031831 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)))