pivot table

[Carolina Girl]

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.

 

[JP]

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

=(OR(AND(VacationStart>BlackOutStart,VacationStart<BlackOutEnd),AND(VacationEnd>BlackOutStart,VacationEnd<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

 

[Jim May]

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<=Sheet2!$C$1:$C$2)>0,0)),"x","")

Hope this helps,

Jim May