+ Reply to Thread
Results 1 to 13 of 13

Need to change formula to look at 2 dates instead of 1

  1. #1
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Need to change formula to look at 2 dates instead of 1

    Hi,

    I have a formula that looks at a date and determines if an action has occured on or after that date and returns the name of the client, I need to formula to look at 2 dates (start of the month and end of the month) and determine if an action has fallen either on those dates or between those dates and return a name.

    the formula is as follows

    =IFERROR(INDEX(Appointments!E:E,SMALL(IF([B]Appointments!$G$5:$G$1232>=$AJ$7,IF(Appointments!$B$5:$B$1232=1,ROW(Appointments!$B$5:$B$1232))),ROWS(AM$10:AM10))),"")

    the date lookup is AJ7 (currenntly the start of the mont, the list of dates to lookup are G5 to G1232, all other parts of the formula work great I just cannont get it to use a start and end date as the lookup values.

    Thanks in advance

    JD

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need to change formula to look at 2 dates instead of 1

    Assuming that you end date is in AJ8 then maybe this unTested(of course) ARRAY formula will works for you!
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    -- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
    Not just Enter.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Need to change formula to look at 2 dates instead of 1

    Hi I can't get it to work I keeps saying there is a problem with the formula at the part Appointments!$B$5:$B$1232=1, I need the end part of the original formula as the formula will be coppied to 40 consecutive cells and part where it says ROWS(AM$10:AM10))),"") progresses and counts the item above as a populated cell, the second date is in AN7.

    Cheers

    JD

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need to change formula to look at 2 dates instead of 1

    JD

    As i have no data to test i believe that the problem should be exactly before the red part of the formula that exist
    a [B] .Try to delete it and test the formula again pls.

    OR else i need a small sample workbook.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need to change formula to look at 2 dates instead of 1

    Hi,

    Is the [B] in there an intentional part of another filename or a typo whilst trying to embolden text in this post?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to change formula to look at 2 dates instead of 1

    Quote Originally Posted by john dalton View Post

    =IFERROR(INDEX(Appointments!E:E,SMALL(IF([B]Appointments!$G$5:$G$1232>=$AJ$7,IF(Appointments!$B$5:$B$1232=1,ROW(Appointments!$B$5:$B$1232))),ROWS(AM$10:AM10))),"")
    In other words, you want to look for dates that are within the month of the date in cell AJ7?

    Is the [B] supposed to be in the formula?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need to change formula to look at 2 dates instead of 1

    Also,

    Fotis - I think you're mixing two different techniques here, aren't you?

    For the SMALL(... either a series of IF(...,IF(,,, etc. OR IF((...)*(...)* etc. but NOT both.

    Regards
    Last edited by XOR LX; 10-16-2013 at 09:31 AM.

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need to change formula to look at 2 dates instead of 1

    Quote Originally Posted by XOR LX View Post
    Also,

    Fotis - I think you're mixing two different techniques here, aren't you?

    For the SMALL(... either a series of IF(...,IF(,,, etc. OR IF((...)*(...)* etc. but NOT both.

    Regards
    Correct. I always hve difficulties when i don't have a sample sheet to test.. So looks like...



    Formula: copy to clipboard
    Please Login or Register  to view this content.


    is ..more correct..!

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to change formula to look at 2 dates instead of 1

    Here's my best guess...

    Array entered**:

    =IFERROR(INDEX(Appointments!E:E,SMALL(IF(TEXT(Appointments!$G$5:$G$1232,"mmmyyyy")=TEXT($AJ$7,"mmmyyyy"),IF(Appointments!$B$5:$B$1232=1,ROW(Appointments!$B$5:$B$1232))),ROWS(AM$10:AM10))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  10. #10
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Need to change formula to look at 2 dates instead of 1

    Hi, The date range is in AJ7 ie 01/10/13 and AN7 31/10/13 I need all names that fall within that date range to be listed, the [B] is an error It is the symbol created when I tried to highlight the text.

    The dates in AJ 7 and AN7 are in normal date format.

    Hope that helps.

    the E:E is the list of names, G is the list of dates, B is a Number generator for a set of criteria and AM10 is the first line of the list to filled.

    JD

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to change formula to look at 2 dates instead of 1

    OK, try this...

    =IFERROR(INDEX(Appointments!E:E,SMALL(IF(Appointments!$G$5:$G$1232>=$AJ$7,IF(Appointments!$G$5:$G$1232<=$AN$7,IF(Appointments!$B$5:$B$1232=1,ROW(Appointments!$B$5:$B$1232)))),ROWS(AM$10:AM10))),"")

    Still array entered.

  12. #12
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Need to change formula to look at 2 dates instead of 1

    Cheers guys thats works brilliantly !

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to change formula to look at 2 dates instead of 1

    You're welcome. We appreciate the feedback!

+ 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. Need formula to change dates based on given year
    By kesmith2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-27-2013, 06:46 AM
  2. [SOLVED] using formula to change date a month on and saving dates
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-02-2013, 05:02 PM
  3. [SOLVED] Choose a Months, and Cells change to those dates Formula
    By ashhm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-21-2012, 07:21 PM
  4. Replies: 3
    Last Post: 07-15-2010, 09:02 AM
  5. formula for referencing dates that change
    By Staberinde in forum Excel General
    Replies: 9
    Last Post: 03-02-2010, 05:30 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