+ Reply to Thread
Results 1 to 3 of 3

Formula to find dates that fall in several ranges

  1. #1
    Registered User
    Join Date
    05-29-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Formula to find dates that fall in several ranges

    I have upto 20 date ranges - the start date is in column A and the end date is in column B - EG
    A B
    1/1/13 10/1/13
    1/2/13 1/2/13
    3/3/13 6/3/13

    I then have a list of dates in colum D - EG
    1/1/13
    2/1/13
    3/1/13
    4/1/13
    all the way to end of year..

    I want a formula that will return a "1" in the corresponding cell in column E if the date falls in any of the date ranges in columns A and B. For example 1/1/13 2/1/13 3/13/13 4/1/13 etc would all have a 1 next to them because they fall in the range 1/1/13 to 10/1/13. Is this possible?

    I'd really appreciate any answers given. Thanks in advance.

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

    Re: Formula to find dates that fall in several ranges

    Try this in E1 and fill down
    =SUMPRODUCT(($A$1:$A$20<=D1)*($B$1:$B$20>=D1))
    Quang PT

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Formula to find dates that fall in several ranges

    Hi Sam,

    I'm not sure I understand the exact format of your question. See if I got it correct in the following example attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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