+ Reply to Thread
Results 1 to 5 of 5

Checking whether date in range

  1. 02-01-2022, 12:22 PM #1
    samparker095
    samparker095 is offline
    Registered User
    Join Date
    02-01-2022
    Location
    London
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2111 Build 16.0.14701.20278) 32-bit
    Posts
    27

    Checking whether date in range

    I have a set of data, which has a column for week beginning & ending.

    I then need to check if a bank holiday falls into these dates. The list of bank holidays is in another list.

    How can I write a formula to check if these dates fall on a bank holiday.

    Please see attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-01-2022, 12:31 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,898

    Re: Checking whether date in range

    Try this with a sample set of dates that includes one of the bank holidays you've listed:

    =IF(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS.INTL(A2,B2,"0000000",$E$2:$E$41)>0,"Yes","")

    I changed Boxing Day 2019 to October just to show it working.

    AliGW on MS365 Insider (Windows) 64 bit

    A
    B
    C
    1
    		 Week Start Week Ending
    2
    02/09/2019
    08/09/2019
    		 No
    3
    09/09/2019
    15/09/2019
    		 No
    4
    16/09/2019
    22/09/2019
    		 No
    5
    23/09/2019
    29/09/2019
    		 No
    6
    30/09/2019
    06/10/2019
    		 No
    7
    07/10/2019
    13/10/2019
    		 No
    8
    14/10/2019
    20/10/2019
    		 No
    9
    21/10/2019
    27/10/2019
    		 Yes
    10
    28/10/2019
    03/11/2019
    		 No
    11
    04/11/2019
    10/11/2019
    		 No
    Sheet: Sheet1

    AliGW on MS365 Insider (Windows) 64 bit

    E
    F
    25
    26/10/2019
    		 Boxing Day
    Sheet: Sheet1
    Attached Files Attached Files
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 02-01-2022, 12:47 PM #3
    samparker095
    samparker095 is offline
    Registered User
    Join Date
    02-01-2022
    Location
    London
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2111 Build 16.0.14701.20278) 32-bit
    Posts
    27

    Re: Checking whether date in range

    Hi, thanks for the prompt response.

    I am trying to do this with tables and seem to be getting error values, could you explain why.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-01-2022, 12:53 PM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,898

    Re: Checking whether date in range

    Your Bank Holiday range was wrong. Try this:

    =IF((([@[Week Ending]]-[@[Week Start]])+1)>NETWORKDAYS.INTL([@[Week Start]],[@[Week Ending]],"0000000",Bank_Holidays[Date]),"Yes","No")

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Reply With QuoteRegister To Reply
  5. 02-02-2022, 12:05 PM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,898

    Re: Checking whether date in range

    Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Start Date and End Date Condition Checking Data Validation Drop Down List
    By reachharry in forum Excel General
    Replies: 1
    Last Post: 05-20-2017, 10:29 AM
  2. [SOLVED] Checking if a date falls within a range
    By TnD_Guy in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-21-2017, 08:06 PM
  3. Checking Range, altering out of bounds, checking result against table
    By Sillystring in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-11-2016, 06:47 AM
  4. [SOLVED] checking if a date/date range already exists in 4 other date storing tables
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2014, 06:01 PM
  5. [SOLVED] checking if a date is in between a certain range. with multiple ranges
    By biddum in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-02-2012, 02:56 PM
  6. Finding the minimum in a range while checking for conditions to exclude cells in the range
    By Banino1234 in forum Excel General
    Replies: 3
    Last Post: 04-01-2012, 05:37 AM
  7. checking values across a date range
    By censura10 in forum Excel General
    Replies: 1
    Last Post: 11-10-2009, 08:45 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1