Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Can you calculate vacation days in a period? [Homework]

Posted on January 25th, 2013 in Excel Challenges - 47 comments

Its Friday, that means time for another Excel challenge for you.

This is based on yesterday’s employee vacation dashboard.

Calculate vacation days in a period:

Calculate vacation days using Excel formulas - formula challenge & homeworkYour mission, if you choose to accept it,

Step 1: Download this workbook.

Step 2: Calculate number of vacations taken in a period. Specifically,

  1. How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
  2. How many vacations are taken such that at least one day of vacation is between start & end dates?
  3. How many people took vacations? (if same person took multiple vacations, then count it as 1)

You are free to,

  • Use helper columns
  • Any formula
  • Play Mission Impossible music in background
  • Drink no more than 2 cups of coffee

How to post your answers?

Simple, just post them in comments. Include explanation of your logic too if possible. Click here to post your answers.

Need some clues?

We have them. See these links:

GO!!!

Added later: Solution for this problem

here were quite a few interesting solutions in the comments.

Today, let me explain how to solve these problems using Excel formulas.

Watch below video:

Download solution workbook

Click here to download the solution work book & see the formulas.

Your email address is safe with us. Our policies

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

47 Responses to “Can you calculate vacation days in a period? [Homework]”

  1. K. NARAYAN says:

    Hi ,
    I think this time , your challenges are quite simple ; I would like to add some more challenges :
    How many of the vacations overlapped with each other ?
    How many vacations did not overlap with each other ?
    What is the maximum number of people who were on vacation at any point of time ?
    What is the maximum period when at least one person was on vacation ?
    What is the maximum / minimum period when no one was on vacation ?
    Probably each of these can be taken up for a forensic post , assuming that each of them can be solved using formulae.

  2. ChrisByham says:

    OK,
     
    I’ve tackled 1) and 2) in similar ways, using COUNTIFS as follows:
     
    1) =COUNTIFS(vacations[Start Date],”>=” & start,vacations[End Date],”<=” & end)
    Counts number of vacations where Start Date is on or later than 01-Feb and End Date is earlier than or on 31-Mar – so entire vacation is within period.
    2) =COUNTIFS(vacations[Start Date],”>=” & start,vacations[Start Date],”<=” & end) + COUNTIFS(vacations[Start Date],”<=” & start,vacations[End Date],”>=” & start)
    Similar to 1st question, but looking for vacations where Start Date is later than 01-Feb and start date is earlier than 31-Mar – this captures all vacations that start in the period. Only vacations that are missing are those that start earlier than period and end once the period has started, which is captured by the
    + COUNTIFS(vacations[Start Date],”<=” & start,vacations[End Date],”>=” & start)
    counting all records where start date is earlier than period start, and end date is later than period start.
    3) There’s probably a nice array formula for this, which I’m going to try and work out. In the meantime, I added a new column, with the formula against the first row as:
    =IF(AND([@[Start Date]]>=start,[@[End Date]]<=end,SUMIF($B$3:B3,[@[Employee Name]],$E$3:E3)=0),1,0)
    So record a 1 if the vacation is fully within the period (start date is later than 01-Feb AND end date is earlier than 31-Mar) AND if the Employee concerned doesn’t already have a 1 against them.
    I hadn’t used SUMIFS until I spotted it on Chandoo a few days ago – such a simple and powerful formula, and I took a guess that a COUNT version would also exist – I’m going to be using these a lot – thanks Chandoo!!

  3. tadovn says:

    https://dl.dropbox.com/u/69797926/vacation-calculations_tadovn.xlsx
     
    Dear,
    I sent the answer for this.
    I use helper column to calculate.
    The formular i used is IF, COUNTIFS, SUM, SUMIFS.
    Thanks,

  4. A. Ramanan says:

    Hi,
    I have converted the Table as normal cells & also additional column (Col E).

    Employee Name - 
    B4

    Start Date - 
    C4

    End Date - 
    D4

    # of days - 
    E4

     
    1. E4 to E123 - =SUMPRODUCT(–(ISNUMBER(MATCH(ROW(INDIRECT(C4&”:”&D4)),ROW(INDIRECT(start&”:”&end)),0))))
    Q1 - =SUMPRODUCT(($C$4:$C$123>=start)*($D$4:$D$123<=end))
    Q2 - =SUMPRODUCT(–($E$4:$E$123<>0))
    Q3 -=SUM(IF(FREQUENCY(IF($B$4:$B$123<>””,IF($E$4:$E$123<>0,MATCH($E$4:$E$123,$E$4:$E$123,0))),ROW($E$4:$E$123)-ROW($E$4)+1),2))

  5. A. Ramanan says:

    The answer for Q3 requires Ctrl + Shift + Enter
     

  6. K. NARAYAN says:

    Some more challenges :
    Who took the longest / shortest vacation , and for how many days ?
    Who was on vacation for the maximum number of days ( this is different from the earlier question , since this includes more than 1 vacation by the same person ) ?
     

  7. Juan Carlos Etayo says:

    I need help

    Problems running macros in Excel 2010 on Macintosh computers. How I can fix this?
     
    Thx.

  8. Mayur Naik says:

    Below are the my solutions :
     
    Q 1>  =IF(C4>start,IF(D4<end,”YES”,”NO”),”NOO”) & Then =COUNTIF($I$4:$I$123,S3)
    Q 2> =IF(AND(start<C4,C4<end),”YES”,IF(AND(start<D4,D4<end),”YES”,”NO”)) & then =COUNTIF($G$4:$G$123,S3)
    Q3> Remove duplicates from Q 1

  9. Saleh Saeed says:

    Hello everybody
    Q1: 
    =SUMPRODUCT(–(vacations[Start Date]>=start),–(vacations[End Date]<=end))
    Q2:
    =SUMIFS(vacations[Column1],vacations[Start Date],”<=”&end,vacations[End Date],”>=”&start)
    Column1= typed 1 in all cells parallel with other columns
    Q3:
    =IF(OR(C5<=start,D5>=end),IF(COUNTIF($B$4:B7,B7)=1,O4+1,O4),O4)
    I get by this formula 13 people
     
    Thank you

  10. Sufal says:

    I am facing some kind of this problem.
    My table is like this
    “Start” column should have the same value as the “End” of Previous Row if the ID is same.
    For example in the 1st row ID is 1 start and end is 0 and 20 respectively.
    In the second row ID is 1. So the Start should be 20. But in table its 18. I need to find out the IDs that have this problem.
    In third row ID is still 1 and “start” is equal to the End of Previous Row having ID 1. So its OK. Can any one give me idea? Thanks
     

    ID
    Start
    End

    1
    0
    20

    1
    18
    40

    1
    40
    230

    2
    0
    250

    2
    250
    1500

    2
    1500
    1530

    3
    0
    15

    3
    15
    30

    3
    28
    40

    3
    40

    50

  11. Ans 1. COUNTIFS(vacations[Start Date],”>=”&start,vacations[End Date],”<=”&end)
    Ans 2. Using a helper column with the number 1 in all rows, i used the formula: SUMIFS(vacations[Column1],vacations[Start Date],”<=”&end,vacations[End Date],”>=”&start)
    Ans 3. I first removed the unique names and used the formula to find the dates using only unique names. Then i used the formula
     Formula 1. COUNTIFS(vacations[Employee Name],O4,vacations[Start Date],”>=”&start,vacations[End Date],”<=”&end)
    Formula 2. COUNTIFS(P4:P29,”>0″) to get 14 as the answer.
    Thanks,
    Mustafa

  12. Raj Kumar Kothari says:

    Hi the following are my answers:
    Q1: =SUM((–((vacations[Start Date])>=start))*(–((vacations[End Date])<=end)))  [CSE]
     
    Q2: =SUM((–((vacations[Start Date])<=end))*(–((vacations[End Date])>=start)))  [CSE]
     
    Q3: This one was little tricky to do it in a single cell (without using helper column), so I copies all the names to a new column N, and removed duplicates (I found 26 unique names in this case) and in the next column I applied the following formula and dragged it down to the last row:
    =IF(SUM(–(N4=vacations[Employee Name])*(–((vacations[Start Date])>=start))*(–((vacations[End Date])<=end)))>0,1,0)  [CSE]
    And, took a sum of all the velues which was equal to 14.
     
    *CSE = Control+Shift+Enter
    Thanks,
    Raj Kumar Kothari

  13. Mitch says:

    So the first two aren’t that hard but the third one was quite a pain to do in one cell. Since most people have already answered the first 2, I won’t bother answering those. But I will post an answer to the third one:
    Enter this as an array formula:
    =SUMPRODUCT(IFERROR(1/COUNTIFS(vacations[Employee Name],vacations[Employee Name],vacations[Start Date],”>” & start,vacations[End Date],”<” & end),0),–(vacations[Start Date]>start),–(vacations[End Date]<end))
     
    The inspiration for this formula comes from a formula chandoo posted a while back on how to count the number of unique items in a list.
     
    Thanks for the great challenge Chandoo!

    • Raj Kumar Kothari says:

      Hi Mitch,
      I copy pastes this formula but didn’t work.
      –(vacations[Start Date]>start),–(vacations[End Date]<end) part of the formula yields an error,
       
      Thanks,
      Raj

  14. Mitch says:

    The “-” should be doubled as in below (I copy/pasted it from excel before but it seems to have changed to single “-” by itself. I’m using excel 2007 (don’t know if that could cause the error)
     
    =SUMPRODUCT(IFERROR(1/COUNTIFS(vacations[Employee Name],vacations[Employee Name],vacations[Start Date],”>” & start,vacations[End Date],”<” & end),0),–(vacations[Start Date]>start),–(vacations[End Date]<end))

  15. Mitch says:

    Turns out it’s not my excel copying but the website that, for some reason, is autocorrecting it to not double the “-”.
    You should have two “-” whenever you see them. Let me know if that works.

  16. Kiran says:

    Hi,
    I have tried the below formule for the 3 questions.
    1. How many vacations are taken in this period?
    A: =SUMPRODUCT((vacations[Start Date]>=start)*(vacations[End Date]<=end))
    2. How many vacations are taken in this period?
    A: =SUMPRODUCT((vacations[Start Date]<end)*(vacations[End Date]>start))
    3. How many distinct people took vacation in this period?
    A: For this, i got some error when written in single cell. So i have taken one helper column. (Column E)
    Filled the column E with ‘=IF(([Start Date]>=start)*([End Date]<=end),[Employee Name],”0″)’
    Used the below formula to get the answer for the question.
    =SUMPRODUCT(1/COUNTIF(vacations[Column1],vacations[Column1]))-1 
     

  17. Smarty says:

    Q-1 Actule Result will be 25 from 1st Jan.12 to 31st Mar.12 with below formula
    =SUMPRODUCT(–(C4:C123>=start)*(D4:D123<=end))
     
    @-2 & Q-3  Pls clear what result u want
     
    Smarty

  18. Smarty says:

    How can i join this group to get & give the Solutions
     
    Smarty

  19. Naina says:

    1. =COUNTIFS(vacations[Start Date],”>=”&start,vacations[End Date],”<=”&end)
    2. =COUNTIFS(vacations[Start Date],”<=”&end,vacations[End Date],”>=”&start)
    3. This isnt working, what am I missing? First of all, its giving me an extra ;0 in the 3rd array, and secondly, my formula isn’t correct, in the sense that the match range should correspond correctly.
    =SUMPRODUCT(–(vacations[Start Date]>start),–(vacations[End Date]<end),IF(FREQUENCY(MATCH(vacations[Employee Name],vacations[Employee Name],0),MATCH(vacations[Employee Name],vacations[Employee Name],0))>0,1,0))
    I guess an array would work..

  20. Q3:
    Column C in ascending order.
    1) Column C even year:
    M16: {=CORRESP(MÊS(start);MÊS($C$4:$C$123);0)+3}
    M17: {=CORRESP(MÊS(end);MÊS($C$4:$C$123);1)+3}
    M18: {=SOMA(1/CONT.SES(INDIRETO(“B” & M16 & “:B” & M17);INDIRETO(“B” & M16 & “:B” & M17)))}
    2) Column C different years:
     
    N16: {=CORRESP(DATA(ANO(start);MÊS(start);1);DATA(ANO($C$4:$C$123);MÊS($C$4:$C$123);1);0)+3}
    N17: {=CORRESP(DATA(ANO(end);MÊS(end);1);DATA(ANO($C$4:$C$123);MÊS($C$4:$C$123);1);1)+3}
    N18: {=SOMA(1/CONT.SES(INDIRETO(“B” & N16 & “:B” & N17);INDIRETO(“B” & N16 & “:B” & N17)))}
     

  21. [...] Friday, we posted a home work problem for you – Calculate vacation days using formulas. There were quite a few interesting solutions in the [...]

  22. Question:
    Suppose we have this 2 dates into the excel vacations file:
    David: Start day: 25/01/2013 & End Day: 4/02/2013.
    In this case David enjoyed 7 job days of his vacations. But also in my report i need to track how many vacations belongs to each month. 
    In this case the answer is 5 days for January and 2 days for February. But I cant figure out how  to get this with any formula.
     Thx for the help..

  23. Manoj Choudhary says:

    ERROR IN THE SOLUTION XLS
     
    Well.. i noticed this for the question2 : how many vacations are taken in this period. With the current solution, any vacation starting before ‘start’ and ending after ‘end’ is calculated twice. An alternative approach to fix this is to subtract vacations starting after the period and vacations ending before the period from the total number of vacations. Here is the formula i used : 
    =COUNTA(vacations[Employee Name])-COUNTIF(vacations[End Date],”<”&start)-COUNTIF(vacations[Start Date],”>”&end)

  24. Q1:
    =CONT.SES(C4:C123;”>=” & start;D4:D123;”<=” & end)
    Q2:
    =CONT.SES(C4:C123;”>=” & start;C4:C123;”<=” & end) + CONT.SES(C4:C123;”<” & start;D4:D123;”>=” & start;D4:D123;”<=” & end)

  25. Naveen says:

    Dear Friends,
    i need someones help to over come the issue i am facing that is i need to remove first orlast letters from the content in a cell
    Ex.
    HEPLANDCCRDDSUA
    last 2 digits, UA needs to be removed
    HOw can i do this as i need to do this for more than thousand
    Thank you,
    Naveen
     

  26. Raj Kumar Kothari says:

    @Navin:
     
    You can use the following formula and drag it down till the desired amount of cells: (Please change the referencing according to your data)
     
    =MID(A1,1,LEN(A1)-2)
    Thanks, Raj

  27. benishiryo says:

    hi Chandoo.  i spotted a discrepancy in Q2′s answer.  if you change C4′s date to be before the Start Date & D4 to be after the End Date, the formula double counts that cell.  
    hence, maybe:
    =SUMPRODUCT((vacations[Start Date]<start)*(vacations[End Date]>=start)+(vacations[Start Date]>=start)*(vacations[Start Date]<=end)*(vacations[End Date]>end))
    or:
    =COUNTIFS(vacations[Start Date],”<”&start,vacations[End Date],”>=”&start)+COUNTIFS(vacations[Start Date],”>=”&start,vacations[Start Date],”<=”&end,vacations[End Date],”>”&end)
    i love your dashboards by the way

    • Rics says:

      Hi Benishiryo, Even I am getting incorrect answer with both the solutions provided by you. Please try below:

      =J8+COUNTIFS(vacations[Start Date],”greater than”&start,vacations[End Date],”greater than equal to”&start)+COUNTIFS(vacations[Start Date],”less than equal to”&end,vacations[End Date],”greater than”&end)

  28. Rics says:

    Reg the video.. I am getting incorrect answers for the second question. if there are vacations that are on the exact start date and end date as specified as start and end.

    Even if one day of the vacation is between start and end dates, you should count it

    I am getting correct answer with following:
    =J8+COUNTIFS(vacations[Start Date],”=”&start)+COUNTIFS(vacations[Start Date],””&end)

    In the video it was mentioned as following, which seems to be incorrect.
    =J8+COUNTIFS(vacations[Start Date],”=”&start)+COUNTIFS(vacations[Start Date],”=”&end)

    Please correct me if I am wrong.

  29. Elkhan says:

    probably not the most sussinct ones, especially for the Q3:

    Q1==SUMPRODUCT((vacations[Start Date]>=start)*(vacations[End Date]<=end))

    Q2=SUMPRODUCT((IF(((start<=vacations[Start Date])*(vacations[Start Date]<=end))+(start<=vacations[End Date])*(vacations[End Date]0,1,0))) CSE

    Q3=SUMPRODUCT(1/(COUNTIF(INDEX(vacations[Employee Name],MATCH(1,(vacations[Start Date]>=start)*(vacations[End Date]=start)*(vacations[End Date]=start)*(vacations[End Date]=start)*(vacations[End Date]<=end),0)),G8-1,0)))) CSE

  30. Elkhan says:

    some rearrangements for Q3 to avoid constant referrence (1) in the MATCH function. it may be useful in case of the same task with Q2 conditions

    Q3=SUMPRODUCT(1/(COUNTIF(INDEX(vacations[Employee Name],ROWS(C4:INDEX(vacations[Start Date],MATCH(TRUE,vacations[Start Date]>=start,0))-ROW(C4)+1)):OFFSET(INDEX(vacations[Employee Name],ROWS(C4:INDEX(vacations[Start Date],MATCH(TRUE,vacations[Start Date]>=start,0))-ROW(C4)+1)),G8-1,0),INDEX(vacations[Employee Name],ROWS(C4:INDEX(vacations[Start Date],MATCH(TRUE,vacations[Start Date]>=start,0))-ROW(C4)+1)):OFFSET(INDEX(vacations[Employee Name],ROWS(C4:INDEX(vacations[Start Date],MATCH(TRUE,vacations[Start Date]>=start,0))-ROW(C4)+1)),G8-1,0)))) CSE

  31. Daniel says:

    For the first two parts I had a slightly different answer, which seemed a little simplier than the given answers (they are both array formulas, and so require control-shift-enter):
    Part 1:
    =SUM(IF(vacations[Start Date]>=start,IF(vacations[End Date]=start,IF(vacations[Start Date]<=end,1,0),0))

  32. Daniel says:

    What is posted above is not my formula. Somehow it was muddled in the posting process. I will try again:

    Part1:
    =SUM(IF(vacations[Start Date]>=start,IF(vacations[End Date]=start,IF(vacations[Start Date]<=end,1,0),0))

    Both array formulas, use with control-shift-enter.

  33. Daniel says:

    Muddled again. I give up.

  34. Bigger Don says:

    hmmm…I got two out of three, and when I check the third mine result coincides with the data
    How many vacations are taken in this period?
    =COUNTIFS(vacations[Start Date],”>=”&start,vacations[End Date],”=”&start,vacations[End Date],”<="&end)
    +COUNTIFS(vacations[Start Date],"<="&start,vacations[End Date],"=”&start)
    +COUNTIFS(vacations[Start Date],”>=”&start,vacations[Start Date],”=”&end)

    How many distinct people took vacation in this period?
    For this I inserted a column in the table, starting in the first row, then copying this down
    =IF(AND(OR(AND([@[Start Date]]>=start,[@[End Date]]<=end),AND([@[Start Date]]<=start,[@[End Date]]=start),AND([@[Start Date]]>=start,[@[Start Date]]=end)),COUNTIFS(C$3:C3,”=1″,B$3:B3,”=” &[@[Employee Name]])<1),1,0)

    Then in H19 (the answer cell) =SUM(vacations[Column1])

    That gave me 15…even hand-counted and sorted for dupes…maybe corrupted my file with a wrong date?
    Name Start Date End Date
    David 1 26-Jan-12 2-Feb-12
    Lance 1 5-Feb-12 14-Feb-12
    Vincent 1 6-Feb-12 12-Feb-12
    Thomas 1 9-Feb-12 15-Feb-12
    Xinhua 1 14-Feb-12 20-Feb-12
    Mindy 1 16-Feb-12 19-Feb-12
    Cindy 1 18-Feb-12 21-Feb-12
    Albert 1 19-Feb-12 22-Feb-12
    Jackie 1 24-Feb-12 4-Mar-12
    Zack 1 29-Feb-12 3-Mar-12
    Ganesh 1 1-Mar-12 7-Mar-12
    Queen 1 3-Mar-12 5-Mar-12
    Barry 1 7-Mar-12 9-Mar-12
    Wendy 1 13-Mar-12 17-Mar-12
    Steve 1 17-Mar-12 21-Mar-12

Leave a Reply