# Can 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.

### Do you want to be awesome in Excel?

**Here is a smart way to become awesome in Excel**. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing **95 Excel tips & tricks**. Please sign-up below:

Your email address is safe with us. Our policies

### Leave a Reply

Designing a dashboard to track Employee vacations [case study] |
Introduction to DAX Formulas & Measures for Power Pivot |

## 49 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 KothariSo 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

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)

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.

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

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

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

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.

Muddled again. I give up.

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

Not a fan of array formulas but the task was fairly small so no impact.

={SUM(1/COUNTIF(INDIRECT(ADDRESS(MATCH(start,vacations[Start Date])+4,2,1,1)&”:”&ADDRESS(MATCH(end,vacations[End Date])+3,2,1,1)),INDIRECT(ADDRESS(MATCH(start,vacations[Start Date])+4,2,1,1)&”:”&ADDRESS(MATCH(end,vacations[End Date])+3,2,1,1))))}

1. Answer=COUNTIFS(vacations[Start Date],”>=”&start,vacations[End Date],”<="&end)

2. Answer=COUNTIFS(vacations[Start Date],"=”&start)

3. I used 2 helper columns – one to test the vacation dates and enter the name of employee and the other to count the employee if it was unique and ignore blank space and duplicate employee names.

column1=IF(AND([@[Start Date]]>=start,[@[End Date]]<=end),[@[Employee Name]],"")

column2 =IF([@Column1]="",0,IF(COUNTIF($E$1:E3,[@Column1])=0,1,0))

Answer=SUM(vacations[Column2])