Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

    Click image for larger version. 

Name:	Example.PNG 
Views:	25 
Size:	28.0 KB 
ID:	6198

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,655
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013

    Return specific date if match between dates

    Quote Originally Posted by Gster View Post
    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
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4
    My apologies PecoFlyer. That's why I'm a Neophyte, right?

    I have attached a sample spreadsheet.

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

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    you are welcome
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Hi navic,

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

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Gster View Post
    What if I wanted to know the INDEX of the date within the list of holidays?
    try
    Code:
    =VLOOKUP(D6;Sheet2!F5:G13;2;FALSE)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  8. #8
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,655
    Articles
    0
    Excel Version
    2010 on Xubuntu
    What do you need this "Index"' for?
    Thank you Ken for this secure forum.

  9. #9
    See the revised spreadsheet with the example.
    Attached Files Attached Files

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013

    VLOOKUP function in Excel

    Quote Originally Posted by Gster View Post
    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
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •