pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carolina Girl - 09 May 2008 16:56 GMT

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.

Reply to this Message

JP - 10 May 2008 19:47 GMT

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

On May 9, 11:56 am, Carolina Girl
<CarolinaG...@discussions.microsoft.com> 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
[quoted text clipped - 13 lines]
> I need to know that John & Jane Does vacation is scheduled on a blackout
> date. Any help would be greatly appreciated.

Reply to this Message

Jim May - 11 May 2008 15:13 GMT

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

Reply to this Message

Rate this thread: