Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date ranges
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€¦. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date ranges
I'm assming you want to compa
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. -- Biff Microsoft Excel MVP "Carolina Girl" wrote in message ... 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.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date ranges
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 "T. Valko" wrote: I'm assming you want to compa 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. -- Biff Microsoft Excel MVP "Carolina Girl" wrote in message ... 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.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date ranges
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 compa .........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! -- Biff Microsoft Excel MVP "Carolina Girl" wrote in message ... 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 "T. Valko" wrote: I'm assming you want to compa 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. -- Biff Microsoft Excel MVP "Carolina Girl" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif Using Date ranges | Excel Discussion (Misc queries) | |||
Date ranges | Excel Worksheet Functions | |||
compare date to various date ranges and sum value | Excel Worksheet Functions | |||
If then Date Ranges | Excel Worksheet Functions | |||
sum of date ranges | Excel Worksheet Functions |