Menu
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:
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.
Related articles:
|
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] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
37 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.
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.
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)))
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")))