Check a list of dates to see if any fall between two dates, return index within list

Gster

New member
Joined
Jan 5, 2017
Messages
5
Reaction score
0
Points
0
I have a list of holidays for a given year (E10-E16). I also have a table which represents the sequential weeks of the year; Weeks 1-52 (B2-M3 is a portion of this table). This table has a column for each week of the year. The first two rows of the table contain the date of the Monday and the Friday in that week e.g. 1/2/2017 and 1/6/2017 respectively for the 1st week of 2017. What I want to achieve is.... for each week, given the "start" and "end" dates for that week, look through the list of holidays and see if any holiday falls between the "start" and "end" dates for that week. If so, I want to know the INDEX of the date within the list of holidays that falls between those two dates. In the example below, I would have a formula in cells B4-M4 that would use this data to indicate whether or not a holiday falls within each week. e.g. D4 and K4 indicate holidays. Thanks in advance for your help!

Example.PNG
 
Hi and welcome
although very nice to look at an image cannot be worked with. Please post a sample sheet showing data and expected results ( Click Go advanced - Manage Attachments).

Thank you
 
Return specific date if match between dates

Thanks in advance for your help!
Well said @pecoflyer, but I have time for you
On Sheet2 put a list of holidays
In cell B4 try next ARRAY formula below (copy the right)
ARRAY or CSE formula should be finished by pressing Ctrl+Shift+Enter (not just enter)

Code:
=IF(AND(IF(COUNTIFS(Sheet2!$B$2:$B$20;">="&B2;Sheet2!$B$2:$B$20;"<="&B3;Sheet2!$B$2:$B$20;"<>")>0;INDEX(Sheet2!$B$2:$B$20;IF(ISNA(MATCH(B$2;Sheet2!$B$2:$B$20));1;MATCH(B$2;Sheet2!$B$2:$B$20)+ISNA(MATCH(B$2;Sheet2!$B$2:$B$20;0))));"")>=B2;IF(COUNTIFS(Sheet2!$B$2:$B$20;">="&B2;Sheet2!$B$2:$B$20;"<="&B3;Sheet2!$B$2:$B$20;"<>")>0;INDEX(Sheet2!$B$2:$B$20;IF(ISNA(MATCH(B$2;Sheet2!$B$2:$B$20));1;MATCH(B$2;Sheet2!$B$2:$B$20)+ISNA(MATCH(B$2;Sheet2!$B$2:$B$20;0))));"")<=B3);IF(COUNTIFS(Sheet2!$B$2:$B$20;">="&B2;Sheet2!$B$2:$B$20;"<="&B3;Sheet2!$B$2:$B$20;"<>")>0;INDEX(Sheet2!$B$2:$B$20;IF(ISNA(MATCH(B$2;Sheet2!$B$2:$B$20));1;MATCH(B$2;Sheet2!$B$2:$B$20)+ISNA(MATCH(B$2;Sheet2!$B$2:$B$20;0))));"");"")

btw: I believe that next time you will attach the Excel file instead of picture
 

Attachments

  • gster-navic7181.xlsx
    12.2 KB · Views: 57
My apologies PecoFlyer. That's why I'm a Neophyte, right? :confused2:

I have attached a sample spreadsheet.

Navic, that formula seems to work. Thanks so much! I need to learn more about array formulas.
 

Attachments

  • HolidayExample.xlsx
    11.2 KB · Views: 43
you are welcome
 
Hi navic,

What if I wanted to know the INDEX of the date within the list of holidays? See attached spreadsheet...yes, I remembered!
 

Attachments

  • HolidayDates2.xlsx
    11.1 KB · Views: 24
See the revised spreadsheet with the example.
 

Attachments

  • HolidayDates3.xlsx
    13.5 KB · Views: 21
VLOOKUP function in Excel

See the revised spreadsheet with the example.
Try in B5 (copy across)
Code:
=IFERROR(VLOOKUP(B6;Sheet2!$F$5:$I$17;4;FALSE);"")
see attachment
 

Attachments

  • gster-navic7181-2.xlsx
    15.1 KB · Views: 22
Navic, I was able to combine your latest formula with your original formula and the combined formula now returns the text related to the holiday date. Awesome! Thanks so much!
 

Attachments

  • HolidayDates4.xlsx
    14.8 KB · Views: 11
Back
Top