Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Searching and returning closest date

This is a discussion on Searching and returning closest date within the Excel Questions forums, part of the Question Forums category; Hello, I could use some help on this one. I need to construct a formula that scans a range of ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    23

    Default

    Hello,

    I could use some help on this one. I need to construct a formula that scans a range of cells containing dates (eg. 29-Jun) and returns data corresponding to the closest date in the range.

    Any suggestions?

    Thanks,

    Dino Vilimek

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,493

    Default

    On 2002-07-05 07:00, SummerStudent wrote:
    Hello,

    I could use some help on this one. I need to construct a formula that scans a range of cells containing dates (eg. 29-Jun) and returns data corresponding to the closest date in the range.

    Any suggestions?

    Thanks,

    Dino Vilimek
    Let A house dates of interest and B the data associated with dates in A.

    Let E1 house a criterion/condition date.

    =INDEX(B:B,MATCH(E1,A:A))

    would suffice, unless you mean something different with "closest date".

    You can also replace A:A and B:B by definite ranges like A2:A10 and B2:B10.

  3. #3
    New Member
    Join Date
    Jun 2002
    Posts
    23

    Default

    Thanks Aladin for looking at my problem. I'm still struggling with it though. The formula is now:

    =INDEX(C7:I7,MATCH("10-Jun-02",C6:I6))

    Problem is, I get an "N/A" error. I suspect it has to do with the "10-Jun-02". Although that is the format in which my dates are displayed, I'm not sure the search is recognizing this format. Any suggestions?

    Thanks again.

    Dino

  4. #4
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    On 2002-07-05 07:27, SummerStudent wrote:
    Thanks Aladin for looking at my problem. I'm still struggling with it though. The formula is now:

    =INDEX(C7:I7,MATCH("10-Jun-02",C6:I6))

    Problem is, I get an "N/A" error. I suspect it has to do with the "10-Jun-02". Although that is the format in which my dates are displayed, I'm not sure the search is recognizing this format. Any suggestions?

    Thanks again.

    Dino
    Excel stores all dates as numbers, regardless of how you have it formatted. To see how excel "sees" a number, change any date format to general. That said, I quick fix is to coerce that reference to a number for excel. this can be done be simply adding zero ("10-Jun-02"+0) or
    =INDEX(C7:I7,MATCH("10-Jun-02"+0,C6:I6))
    if you want to include a "hardcoded" date in your formula.

    Lastly, be aware this formula uses "The Price is Right" logic, where your winner is closest to the date, without being past the date.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Dino:
    Welcome to the Board!

    Couple of things -- you may have to use datevalue("10-Jun-02"); and also the argument 0 , if your data is not in order ... see the worksheet simulation:

    ******** LANGUAGE="JavaScript" ************************************************************************>
    Microsoft Excel - Book1___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    C
    D
    E
    F
    G
    H
    I
    1
    *******
    2
    *******
    3
    entry4******
    4
    *******
    5
    *******
    6
    5-Jun-026-Jun-0215-Jun-0210-Jun-028-Jun-0211-Jun-023-Jun-02
    7
    entry1entry2entry3entry4entry5entry6entry7
    8
    *******
    Sheet3*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Regards!

    [ This Message was edited by: Yogi Anand on 2002-07-05 08:49 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,493

    Default

    On 2002-07-05 08:46, Yogi Anand wrote:
    Hi Dino:
    Welcome to the Board!

    Couple of things -- you may have to use datevalue("10-Jun-02"); and also the argument 0 , if your data is not in order ... see the worksheet simulation:
    ...

    =INDEX(C7:I7,MATCH(DATEVALUE("10-Jun-02"),C6:I6,0))

    ....

    1) There is no need for DATEVALUE. As IML noted,

    "10-Jun-02"+0

    will do.

    2. The formula you suggest fails to take into consideration the original poster's spec closest date in the range in the request. The formula

    =INDEX(C7:I7,MATCH("10-Jun-02"+0,C6:I6))

    with ranges specified and the lookup date hard-coded, on the other hand, will give the desired result if C6:I6 is sorted in ascending order, a default expectation with dates, with the caveat that "be aware this formula uses "The Price is Right" logic, where your winner is closest to the date, without being past the date," as nicely put by IML.

    If C6:I6 is NOT sorted, your suggested formula will not find closest date in the range if the lookup date is not in the range. And, that requires a totally different formula.



  7. #7
    New Member
    Join Date
    Feb 2007
    Posts
    18

    Default Re: Searching and returning closest date

    Would it be possible to add to this formula?

    In column A, I have Workers (several instances)
    Column B, would have the dates they performed a report
    Column C, would be the data (hours worked) for that date

    Cell E1 would have the criterion worker's name.
    Cell F1 would have the criterion date.

    I just need to find the closest date that matches with the worker and the date. However, I would like the date to be the earlier one.

    So, if the criterion date is 5/5/2010.

    There are two dates associated with the Joe Smith.

    5/10/2010 and 4/4/2010, I would want 4/4/2010.

    Thanks!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,493

    Default Re: Searching and returning closest date

    Quote Originally Posted by justcurry View Post
    Would it be possible to add to this formula?

    In column A, I have Workers (several instances)
    Column B, would have the dates they performed a report
    Column C, would be the data (hours worked) for that date

    Cell E1 would have the criterion worker's name.
    Cell F1 would have the criterion date.

    I just need to find the closest date that matches with the worker and the date. However, I would like the date to be the earlier one.

    So, if the criterion date is 5/5/2010.

    There are two dates associated with the Joe Smith.

    5/10/2010 and 4/4/2010, I would want 4/4/2010.

    Thanks!
    That requires a different type of formula...

    G1, control+shift+enter, not just enter:

    =MAX(IF($A$2:$A$10=E1,IF($B$2:$B$10<=F1,$B$2:$B$10)))
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Feb 2007
    Posts
    18

    Default Re: Searching and returning closest date

    Aladin,

    Thank you as always. I have yet to try it out due to deadlines, but will let you know how it works out (though I'm sure it'll work great!).

    Dan

  10. #10
    New Member
    Join Date
    Feb 2007
    Posts
    18

    Default Re: Searching and returning closest date

    Hi Aladin,

    I've tried the formula and it didn't quite workout the way I expected.

    So, in column A I have multiple instances of John Smith and Dave Wall.
    Column B are different dates for each person (the date is the unique identifier for the workers)
    Column C will tell me how many hours they worked on those days.

    So, now I want to find out the Column C info based on two criteria, name and date closest to the date i choose.

    John Smith 1/22/2010 5
    John Smith 2/02/2010 4
    John Smith 3/15/2010 7
    John Smith 4/22/2010 10
    Dave Wall 2/24/2010 8
    Dave Wall 3/1/2010 9
    Dave Wall 3/15/2010 10


    1st criteria is John Smith
    2nd criteria is 4/1/2010
    So it should return 7 ideally.

    Is there a formula that will return that value?

    Thanks,
    Dan

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com