SubscribeCan you calculate vacation days in a period? [Homework]
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:
Your mission, if you choose to accept it,
Step 1: Download this workbook.
Step 2: Calculate number of vacations taken in a period. Specifically,
- How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
- How many vacations are taken such that at least one day of vacation is between start & end dates?
- 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.
| ||||
|
| ||||
|
Leave a Reply
 |
Designing a dashboard to track Employee vacations [case study] | Introduction to DAX Formulas & Measures for Power Pivot |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
39 Responses to “Can you calculate vacation days in a period? [Homework]”
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.
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!!
Interesting!
Hi ..
I tried to use your formulas which is really interesting but it doesn’t work with me
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,
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))
@A. Ramanan
Hi..
Nice work but I wanna see Q3 form because it is not clear..
The answer for Q3 requires Ctrl + Shift + Enter
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 ) ?
I need help
Problems running macros in Excel 2010 on Macintosh computers. How I can fix this?
Thx.
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
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
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
Could you upload excel file, pls?
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
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
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!
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
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))
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.
Hey Mitch, I tried using the double negative, but didn’t work.
Thanks, Raj
Try this. It has the spreadsheet.
http://www.excelforum.com/excel-formulas-and-functions/894788-unique-count-with-criteria.html?p=3100520#post3100520
Hi Mitch, Thanks for your efforts for sharing the link. I really appreciate your help. And yes, this your formula is working fine. Great trick.
Thanks , Raj
Hi Mitch,
Thanks for the solution. A question why we have two dash in front “–”
in the formula.
=SUMPRODUCT(–(vacations[Start Date]>start),–(vacations[End Date]<end))
Thanks
http://www.k2e.com/tech-update/tips/143-using-two-minus-signs-in-excel
to understand usage of double minus signs
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
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
How can i join this group to get & give the Solutions
Smarty
@Smarty
Start here: http://chandoo.org/wp/welcome/
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..
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)))}
[...] Friday, we posted a home work problem for you – Calculate vacation days using formulas. There were quite a few interesting solutions in the [...]
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..
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)
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)
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
@Naveen
If it is always 2 characters
=Left(A1,Len(A1)-2)
Otherwise what are the rules for determining how many characters it is you want to keep
@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
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