Searching and returning closest date

SummerStudent

New Member
Joined
Jun 16, 2002
Messages
23
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Book1
CDEFGHI
1
2
3entry4
4
5
65-Jun-026-Jun-0215-Jun-0210-Jun-028-Jun-0211-Jun-023-Jun-02
7entry1entry2entry3entry4entry5entry6entry7
8
Sheet3
</SPAN>

Regards!
This message was edited by Yogi Anand on 2002-07-05 08:49
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top