Search

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

Excel Challenges - 37 comments

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:

You want to answer below three questions:

1. How many employees are on leave during Easter holidays (14th of April to 28th of April)?
2. How many employees are on approved vacation during Easter holidays?
3. 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.

Want some hints..?

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

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.

### Related articles:

 Written by Chandoo Tags: between formula, challenge, date and time, excel formulas, homework, logical operators in excel Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

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

1. Abhay says:

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

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.

• Martin says:

Hi Chandoo! I'm resolved Using Pivot Tables and filtering by Dates filtes. I Got:

Q1 = 272 | Q2 = 115 | Q3 = 5

I did 3 pivot tables, the first one Leave Start and Leave end in Rows,
Status in columns and Status in values, after that, I only used for Leave Start: Date Filters, Is after and specified is after: 4/13/17 for Leave End, Date filters, is Before 4/29/17 and Booom! the second and third pivot table I only used some filters trick and ready..!

I think than my results are corrects!
Regars! From Tijuana Mexico.

2. prashant says:

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)

3. Amol says:

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

4. Suryakant says:

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

• Desk Lamp says:

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)

• Desk Lamp says:

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.

• sunrise06 says:

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

5. Amol says:

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.

6. Amol says:

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.

7. Josh says:

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.

• Josh says:

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

8. David N says:

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

9. Sujeet Jaiswal says:

1)340
2)242
3)18

• Sujeet Jaiswal says:

3)5 is right answer

10. Sujeet Jaiswal says:

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

11. Nils says:

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

12. Luís Pires says:

Q1) 227
Q2) 199
Q3) 5

• Luís Pires says:

Sorry!

New Numbers:
Q1) 267
Q2) 237
Q3) 4

13. Robert says:

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")

14. Ankur, IncomeBoy says:

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

15. jomili says:

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.

• jomili says:

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

• jomili says:

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.

• jomili says:

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

• jomili says:

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")

16. Robert says:

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")

17. Pavan Sistla says:

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)

18. Mamdouh Elfors says:

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

• Jomili says:

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?

19. Pavan Sistla says:

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 🙂

20. Abbas says:

Q1:

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

338

• Abbas says:

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

21. Brijesh says:

1. 267
2. 237
3. 4

22. PARTHIV says:

267
127
2

23. Cristopher says:

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

24. Ricardo says:

Sorry for the mistakes!!
First I have created a table column named Ninja? with this formula:
=SI(ESERROR(BUSCARV([@[Emp Number]];ninja;1;FALSO));"N";"S")
This tells me whether the employee belongs to Ninja Team.
I've only used the sumproduct function filtering all the columns based on the example request.
Finally, my results are:
Q1: 272
=SUMAPRODUCTO((lvs[Leave Start]>=\$P\$4)*(lvs[Leave End]=\$P\$4)*(lvs[Leave End]=\$P\$4)*(lvs[Leave End]<=\$P\$5)*(lvs[Ninja?]="S")*(lvs[Leave Type]="Vacation")*((lvs[Status]="Approved")+(lvs[Status]="Pending")))

 « 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] »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.