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

Posted on April 7th, 2017 in Excel Challenges - 35 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:

easter-leaves-problem

Click here to download the sample file.

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.

Go ahead and post your questions. 

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.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

35 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:

    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.

  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

  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

  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:

      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?

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

    ANSWER
    338

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

Leave a Reply