pivot table
 

I have been tasked with putting together a pivot table for my group that will
highlight if a persons vacation falls within blackout dates. Right now I have
a list of names and a start and end date for there vacations. I also have a
list of blackout dates set up with start and end dates. Not sure about where
to start or if this can be done? Below is what I have so far
Tab 1
A B C
1 John Doe 10/1/08 10/8/08
2 Jane Doe 1/2/08 1/9/08

Tab 2
A B C
1 blackout A 1/7/08 1/20/08
2 blackout B 9/28/08 10/2/08

I need to know that John & Jane Does vacation is scheduled on a blackout
date. Any help would be greatly appreciated.

pivot table
 

Why bother with a pivot table, this formula returns TRUE if the
person's vacation start date falls inside the blackout period.

=(OR(AND(VacationStartBlackOutStart,VacationStart <BlackOutEnd),AND(VacationEndBlackOutStart,Vacati onEnd<BlackOutEnd)))

where

VacationStart = named cell with vacation start date
VacationEnd = named cell with vacation end date
BlackOutStart = named cell with blackout start date
BlackOutEnd = named cell with blackout end date

I didn't account for holidays or weekends, but this should be a start.

HTH,
JP

On May 9, 11:56 am, Carolina Girl
wrote:
I have been tasked with putting together a pivot table for my group that will
highlight if a persons vacation falls within blackout dates. Right now I have
a list of names and a start and end date for there vacations. I also have a
list of blackout dates set up with start and end dates. Not sure about where
to start or if this can be done? Below is what I have so far
Tab 1
A B C
1 John Doe 10/1/08 10/8/08
2 Jane Doe 1/2/08 1/9/08

Tab 2
A B C
1 blackout A 1/7/08 1/20/08
2 blackout B 9/28/08 10/2/08

I need to know that John & Jane Does vacation is scheduled on a blackout
date. Any help would be greatly appreciated.

pivot table
 

Paste this single formulainto cell D1 and copy down

=IF(ISNUMBER(MATCH(TRUE,(B1=Sheet2!$B$1:$B$2)*(B1 <=Sheet2!$C$1:$C$2)+(C1=Sheet2!$B$1:$B$2)*(C1<=Sh eet2!$C$1:$C$2)0,0)),"x","")

Hope this helps,

Jim May

"Carolina Girl" wrote:

I have been tasked with putting together a pivot table for my group that will
highlight if a persons vacation falls within blackout dates. Right now I have
a list of names and a start and end date for there vacations. I also have a
list of blackout dates set up with start and end dates. Not sure about where
to start or if this can be done? Below is what I have so far
Tab 1
A B C
1 John Doe 10/1/08 10/8/08
2 Jane Doe 1/2/08 1/9/08

Tab 2
A B C
1 blackout A 1/7/08 1/20/08
2 blackout B 9/28/08 10/2/08

I need to know that John & Jane Does vacation is scheduled on a blackout
date. Any help would be greatly appreciated.