+ Reply to Thread
Results 1 to 6 of 6

=IF with criteria matching with Dates

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Boston, USA
    MS-Off Ver
    2019
    Posts
    3

    =IF with criteria matching with Dates

    Hey all,

    I know there is a similar thread out there- but not quite what i'm after..

    Basically I want to return a "True or False" if a date falls in between a date on another data tab. I also want that to only occur for certain data, based on a criteria of a unique ID number.

    I currently have =SUMIFS(ID Range,ID range,ID number,Start Date Range,">="&Start Date,End date Range,"<="&End Date)>0

    This only works if the the period is only one day, but doesn't populate "True" for anything with a multiple day date range.


    Any ideas?

    Thanks!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: =IF with criteria matching with Dates

    this is how I might go about it, but it is simplistic based on an interpretation of what you wrote...
    =IF(AND(B2>=VLOOKUP(A2,Sheet2!$A:$C,2,0),B2<=VLOOKUP(A2,Sheet2!$A:$C,3,0)),TRUE,FALSE)
    So, B2 is where the date is for A2 which has the ID number. I'm looking it up in sheet2 where the ID numbers are in col A and col B has the start date and col C has the end date.
    Perhaps you can adapt it from there.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    Boston, USA
    MS-Off Ver
    2019
    Posts
    3

    Re: =IF with criteria matching with Dates

    Awesome - that works. However, I misspoke in saying that it is a unique number - this is more on an ID number which appears multiple times. I'd want to ensure all of them are considered in the "True / false" rather than the first one which appears.

    Thanks!
    Attached Files Attached Files
    Last edited by AliGW; 07-17-2019 at 12:18 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: =IF with criteria matching with Dates

    so when you note that you want to ensure all of them are considered, what do you mean? Do you mean that if the Jan 6 2019 is not within the range then ALL the others for January would be false?

  5. #5
    Registered User
    Join Date
    07-16-2019
    Location
    Boston, USA
    MS-Off Ver
    2019
    Posts
    3
    Right, if Jan 6th is not present at all for that ID, it should be false. But if the date, or between the two dates, is present in one or more times (for that ID) that date should return "True"
    Last edited by AliGW; 07-17-2019 at 12:18 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: =IF with criteria matching with Dates

    If I understand then this should work.
    1. Place the following formula into cell C2 on the Data sheet and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Test by changing cell C3 on the 2019 Management sheet to 1/6/2019
    Note that this method would not work if there was a discontinuous range, as in if the date in C2 on the 2019 Management sheet was changed to 1/2/2019. If that is a possibility we will need to rethink the proposed solution.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. vba code for matching criteria and dates the dont change
    By steddas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-24-2018, 01:36 PM
  2. Overlapping Dates by Matching Criteria
    By hkdata in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2018, 02:34 PM
  3. Matching dates with different formats and multiple criteria
    By Guading in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2015, 05:23 AM
  4. Matching Criteria from adjacent dates
    By pickslides in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-06-2013, 11:37 PM
  5. Count if between two dates and matching criteria
    By RichST in forum Excel General
    Replies: 2
    Last Post: 10-12-2010, 07:55 AM
  6. Counting the Number of Dates with Matching Month Criteria
    By zom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2008, 07:47 PM

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