date ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carolina Girl - 09 May 2008 18:33 GMT

This is probable very simple and I am just overlooking it. But can some tell
me how to find out if a date range falls between or overlaps another date
range?? Tab 1 contains start and stop vacation dates, tab 2 contains start
and stop blackout dates. I need to list on Tab C2 if the persons vacation is
in a blackout date or not.

Tab1
A    B
1 Start     End
2 1/1/08    5/1/08
3 2/1/08    2/30/08

Tab2
A    B
1 Start     End
2 2/1/08    6/1/08
3 2/1/08    2/30/08

Thanks in advance….

Reply to this Message

T. Valko - 09 May 2008 19:41 GMT

I'm assming you want to compare:

1/1/08 - 5/1/08 to 2/1/08 - 6/1/08
2/1/08 - 2/30/08 to 2/1/08 - 2/30/08

On Sheet2:

=MAX(0,MIN(B1,Sheet1!B1)-MAX(A1,Sheet1!A1)+1)>0

Copy down as needed.

Signature

Biff
Microsoft Excel MVP

> This is probable very simple and I am just overlooking it. But can some
> tell
[quoted text clipped - 17 lines]
>
> Thanks in advance..

Reply to this Message

Carolina Girl - 09 May 2008 20:12 GMT

I'm sorry this was unclear I was unsure of how to explain what I need to find
out is if 1/1/08 – 5/1/08 (my vacation) falls anywhere within the dates of
2/1/08 - 6/1/08 (Blackout date 1) it would, so this would be a yes and
2/1/08-2/30/08 (another vacation) also falls within the 1/1/08-5/1/08
(Blackout date 1) dates so it would also be a yes on the other hand
5/2/08-6/2/08 (another vacation) would not so it would be a no.

Thanks for the Help

> I'm assming you want to compare:
>
[quoted text clipped - 28 lines]
> >
> > Thanks in advance..

Reply to this Message

T. Valko - 09 May 2008 22:07 GMT

Aside from the typo in the date (2/30/08) I think what I suggested does what
you asked for (although the formula returns TRUE or FALSE rather than yes or
no) but I'm confused.

From this most recent reply you want to compare:

........Sheet1...................Sheet2.......
1/1/08 - 5/1/08........2/1/08 - 6/1/08
2/1/08 - 2/30/08......2/1/08 - 6/1/08

Why are there 2 sets of dates on sheet2?

I'm *really* confused!

Signature

Biff
Microsoft Excel MVP

> I'm sorry this was unclear I was unsure of how to explain what I need to
> find
[quoted text clipped - 41 lines]
>> >
>> > Thanks in advance..

Reply to this Message