+ Reply to Thread
Results 1 to 3 of 3

Check if date falls b/twn start date in one array and end date in 2nd arr & return value

  1. #1
    Forum Contributor
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Check if date falls b/twn start date in one array and end date in 2nd arr & return value

    Hi All,

    Does anyone ever get to that point where you're working on Excel for so long that you forget how to put together a simple formula? Well, I think I'm at that point.

    With reference to the attached, I have a string of periods. Each period has a start date and end date. The start date of the second period is the day after the end date of the first period and so on. Each period has a value assigned to it.

    What I need is to create a formula in cell G4 that checks which of the periods that the date in cell F4 falls into, then returns the value for that period. I'm getting Excel block here; I know how to do this using long code, but I was hoping to create a very short code that basically references columns as opposed to a cell range i.e. date in F4 must be greater or equal to date in column B and also less than or equal to date (on the same row) in column C.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Check if date falls b/twn start date in one array and end date in 2nd arr & return val

    Try

    =INDEX(D$4:D$10,MATCH(F4,B$4:B$10,1),1)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,002

    Re: Check if date falls b/twn start date in one array and end date in 2nd arr & return val

    =lookup(f4,b4:b10,d4:d10)
    since your end date+1 is start of next row the above formula will work for you
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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. Replies: 7
    Last Post: 07-14-2017, 12:04 PM
  2. check in which range a date falls and return a specific value
    By beltzer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2016, 04:29 PM
  3. Replies: 31
    Last Post: 09-21-2014, 07:50 PM
  4. Counting if data falls on date between Start Date, End Date or Possible End Date
    By JessHasQuestions in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-26-2014, 08:01 AM
  5. [SOLVED] If statement to check if date falls between date range
    By mcranda in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-21-2013, 07:31 PM
  6. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  7. Replies: 3
    Last Post: 10-13-2012, 08:09 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