+ Reply to Thread
Results 1 to 13 of 13

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

  1. 10-16-2013, 07:51 AM #1
    john dalton is offline
    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
    Reply With QuoteRegister To Reply
  2. 10-16-2013, 08:09 AM #2
    Fotis1991 is offline
    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.
    Reply With QuoteRegister To Reply
  3. 10-16-2013, 09:16 AM #3
    john dalton is offline
    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
    Reply With QuoteRegister To Reply
  4. 10-16-2013, 09:21 AM #4
    Fotis1991 is offline
    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.
    Reply With QuoteRegister To Reply
  5. 10-16-2013, 09:26 AM #5
    XOR LX is offline
    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/
    Reply With QuoteRegister To Reply
  6. 10-16-2013, 09:26 AM #6
    Tony Valko is offline
    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.
    Reply With QuoteRegister To Reply
  7. 10-16-2013, 09:28 AM #7
    XOR LX is offline
    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.
    Reply With QuoteRegister To Reply
  8. 10-16-2013, 09:33 AM #8
    Fotis1991 is offline
    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..!
    Reply With QuoteRegister To Reply
  9. 10-16-2013, 09:47 AM #9
    Tony Valko is offline
    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.
    Reply With QuoteRegister To Reply
  10. 10-16-2013, 10:21 AM #10
    john dalton is offline
    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
    Reply With QuoteRegister To Reply
  11. 10-16-2013, 10:27 AM #11
    Tony Valko is offline
    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.
    Reply With QuoteRegister To Reply
  12. 10-16-2013, 11:25 AM #12
    john dalton is offline
    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 !
    Reply With QuoteRegister To Reply
  13. 10-16-2013, 01:57 PM #13
    Tony Valko is offline
    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 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)

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. How do I use autofill to change the dates in a COUNTIF formula?
    By viper50 in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1