+ Reply to Thread
Results 1 to 10 of 10

Adding date range to formula

Hybrid View

  1. 10-25-2011, 04:00 PM #1
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Adding date range to formula

    The following formula is in cell A10. Right now the formula returns data if the date in B5 matches with a date in column L on the Enter Executions worksheet. I would like to change the formula to return data if the dates in BOTH B5 and B6 match with a date in column L on the Enter Executions worksheet. Can anyone help?

    =IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$5,IF('Enter Executions'!$L$2:$L$100000<$B$5+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))),ROWS($A$1:$A1))),"")
    Attached Files Attached Files
    Last edited by rhudgins; 10-26-2011 at 02:55 PM.
    Reply With QuoteRegister To Reply
  2. 10-25-2011, 05:06 PM #2
    BROWN_RY
    BROWN_RY is offline
    Registered User
    Join Date
    09-29-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding date range to formula

    if teh dates are different in b5 & b6 try an if(or( statement before your equation.
    if the dates are the exact same in b5 & b6 then try this. if you create another column next to your two date columns and concatenate the two dates together, i.e.- a1= 10/2/11 & b1= 10/2/11, then =concatenate(b5," ",b6) and excel will return two serial numbers i.e.- 40553 40553 in b7 lets say. then turn your date that you are referencing in Column L into a serial number by making the format a text. Then you can do an if(or(left(b7,5),right(b7,5)=L,your equation,"").

    im sure theres other ways to do this but when using dates, the concatenation is a useful way to reference. If this is confusing type into google, "double vlookup" and you should find whay youre looking for.
    Reply With QuoteRegister To Reply
  3. 10-26-2011, 11:00 AM #3
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Adding date range to formula

    The dates are different in B5 & B6. Below I tried an if (or( statement in cell A10. The second logical of the If(or( statement references cell B$6. I am recieving an error that the formula contains an error. Any ideas why?

    ={IF(OR(IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$5,IF('Enter Executions'!$L$2:$L$100000<$B$5+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))),ROWS($A$1:$A1))),""),IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$6,IF('Enter Executions'!$L$2:$L$100000<$B$6+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))),ROWS($A$1:$A1))),"")))}
    Reply With QuoteRegister To Reply
  4. 10-26-2011, 01:21 PM #4
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Adding date range to formula

    Can anyone help me with this?
    Reply With QuoteRegister To Reply
  5. 10-26-2011, 01:33 PM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding date range to formula

    Try this:

    =IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$5,IF('Enter Executions'!$L$2:$L$100000<$B$5+1,IF('Enter Executions'!$L$2:$L$100000>=$B$6,IF('Enter Executions'!$L$2:$L$100000<$B$6+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))))),ROWS($A$1:$A1))),"")
    it assumes the same logic, that you want to check L against >=B6 and <B6+1...

    Note: this is really a processor intense formula... if you can reduce those ranges that would be best.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  6. 10-26-2011, 01:47 PM #6
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Adding date range to formula

    Thanks for your help. I applied the formula that you provided me to cell A10 and it is not returning a value. Can you take a look at my workbook and see what may be the problem?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-26-2011, 01:52 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding date range to formula

    This part: IF('Enter Executions'!$L$2:$L$100000<$B$5+1 and then this part IF('Enter Executions'!$L$2:$L$100000>=$B$6 are contradictory. B5 is Oct 18 and so column L must be less than Oct 18 + 1 (or Oct 19).. and at the same time B6 is Oct 19 and you are telling it that column L must be greater than or equal to Oct 19)...

    what exactly are the conditions you need?
    Reply With QuoteRegister To Reply
  8. 10-26-2011, 02:20 PM #8
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Adding date range to formula

    For the date conditions I am looking to return all of the values in the range A$2:A$100000 on the Enter Executions worksheet who's date occurs on 10/18/11 or 10/19/11 (the values in cell B5 and B6). Occasionally there will be no date at all in cell B6(blank cell) and therefor the formula should just return values that occur on the date in cell B5
    Last edited by rhudgins; 10-26-2011 at 02:27 PM.
    Reply With QuoteRegister To Reply
  9. 10-26-2011, 02:35 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding date range to formula

    Try

    =IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF(('Enter Executions'!$L$2:$L$100000=$B$5)+('Enter Executions'!$L$2:$L$100000=$B$6),ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1)),ROWS($A$1:$A1))),"")
    CSE confirmed
    Reply With QuoteRegister To Reply
  10. 10-26-2011, 02:55 PM #10
    rhudgins
    rhudgins is offline
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Adding date range to formula

    Thank you this is what I am looking for
    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)

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