Searching and returning closest date

Thanks:  0
Likes:  0

# Thread: Searching and returning closest date

1.
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. 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. 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. 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. 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
 C3 =

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. 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. ## 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. ## Re: Searching and returning closest date

Originally Posted by justcurry
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)))

9. ## Re: Searching and returning closest date

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. ## Re: Searching and returning closest date

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

## User Tag List

#### Posting Permissions

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