+ Reply to Thread
Results 1 to 11 of 11

Excel lookup between 2 Dates if Holiday Occurs

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    Microsoft Excel 2019 (Windows 11 64-bit).
    Posts
    41

    Excel lookup between 2 Dates if Holiday Occurs

    I am working with an Excel Spreadsheet with a lot of dates. Attached is a sample file with a few example dates. The first Worksheet is called "Dates".
    in Column A is the Begin Date and Column B is End Date.
    Column C is Holiday. I need to verify if a Holiday occurs between the 2 dates. I don't need a Count of Holidays, just to return a value of "YES" or "1" if a Holiday occurs between the date range in each row. The spreadsheet has a second worksheet, called "Holidays" that I wanted to use as a lookup table. Any ideas on how this can be done with an Excel formula in Column C of the "Dates" worksheet? Thanks very much for any assistance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Excel lookup between 2 Dates if Holiday Occurs

    C2=IF($A2<>"",IF(SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT($A2&":"&$B2)),Holidays!$B$2:$B$12,0))))>=1,"yes","no"),"")

    Copy down

  3. #3
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    Microsoft Excel 2019 (Windows 11 64-bit).
    Posts
    41

    Re: Excel lookup between 2 Dates if Holiday Occurs

    CARACALLA. Thanks very much for the quick reply. This is exactly what I needed. I really appreciate it.

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Excel lookup between 2 Dates if Holiday Occurs

    You are welcome

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Excel lookup between 2 Dates if Holiday Occurs

    Why use volatile functions? Why count holidays? Since the holidays are in ascending order,

    C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    Microsoft Excel 2019 (Windows 11 64-bit).
    Posts
    41

    Re: Excel lookup between 2 Dates if Holiday Occurs

    HI hrlngrv,
    thanks very much for this more simplified solution. I was having a challenge getting my head around the first solution. Both of them work, but I do prefer the second in this instance. Thanks again.

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Excel lookup between 2 Dates if Holiday Occurs

    One way:

    =COUNTIFS(Holidays!$B$2:$B$12,">="&Dates!A2,Holidays!$B$2:$B$12,"<="&Dates!B2)

  8. #8
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    Microsoft Excel 2019 (Windows 11 64-bit).
    Posts
    41

    Re: Excel lookup between 2 Dates if Holiday Occurs

    Hi Phuocam,
    For the first part I was only looking for YES / NO rather than the Count. But this solution is excellent as well, as that was going to be my next inquiry is how to do a Count as well. Thanks very much!

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Excel lookup between 2 Dates if Holiday Occurs

    =IF(COUNTIFS(Holidays!$B$2:$B$12,">="&Dates!A2,Holidays!$B$2:$B$12,"<="&Dates!B2)>0,"Yes","No")

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Excel lookup between 2 Dates if Holiday Occurs

    Alternative, holidays count:

    Please Login or Register  to view this content.
    Quang PT

  11. #11
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    Microsoft Excel 2019 (Windows 11 64-bit).
    Posts
    41

    Re: Excel lookup between 2 Dates if Holiday Occurs

    HI bebo021999, Thanks for the info. I was only looking for a count based on the lookup table provided. However, getting the count of business days that occur between the days, is another count I was going to be researching as well. I never heard of the function NETWORK.INTL until n ow, but it looks like it can be used for finding the work days (Monday through Friday), rather than (Monday to Sunday). I will have to research this further. Thanks very much for posting this solution.
    Last edited by revocats10; 11-13-2020 at 12:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. working on a holiday planner based on various holiday anniversary dates
    By marktc19 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 09:26 AM
  2. Lookup Date in Table of Holiday Dates
    By ExcelSponge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2015, 03:51 PM
  3. Work out diffrence between 2 dates minus a couple of holiday range dates
    By wogboy112 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2015, 07:04 AM
  4. [SOLVED] IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates
    By jowarks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2013, 05:42 AM
  5. [SOLVED] Lookup a value that occurs more than once in a column
    By Harribone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2013, 08:03 AM
  6. holiday dates
    By bucci in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-15-2006, 04:40 AM
  7. Holiday Dates
    By Alpur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2005, 02:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1