How to find the closest date

H

haminh

New Member
Joined
Jun 27, 2007
Messages
13
Hi everyone,

I have 2 columns, one has couple dates such as 2/15/2006, 5/10/2006, 5/15/2006, 5/20/2006, 8/15/2006, and 10/15/2006, the other one has a value of 6/15/2006. I want to find the closest date before 6/15/2006. Could you please help me out with this problem?
Thanks in advance.

Best regards,
Haminh
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
If the column of dates is always in ascending order (later dates at the bottom), you can use the MATCH() function. Let's say 6/15/2006 is in A1, and the other dates are in B1:B10.

=INDEX($B$1:$B$10, MATCH(A1, $B$1:$B$10, 1))

Will return the closest date before the date in A1.
 
Upvote 0
Are the dates sorted in Ascending order?
Do you want
A. the closest date BEFORE 6/15/2006 ?
B. the closest date PAST 6/15/2008
C. the closest date regardless if before or past 6/15/2006 ?
 
Upvote 0
iliace said:
If the column of dates is always in ascending order (later dates at the bottom), you can use the MATCH() function. Let's say 6/15/2006 is in A1, and the other dates are in B1:B10.

=INDEX($B$1:$B$10, MATCH(A1, $B$1:$B$10, 1))

Will return the closest date before the date in A1.
Click to expand...

Thanks so much for your quick response. However, these were only few numbers that I made up as an example. In fact, the column date is not in ascending order and we could not sort it in ascending order. Could you please give me a hand
 
Upvote 0
jonmo1 said:
Are the dates sorted in Ascending order?
Do you want
A. the closest date BEFORE 6/15/2006 ?
B. the closest date PAST 6/15/2008
C. the closest date regardless if before or past 6/15/2006 ?
Click to expand...

The dates did not sort in ascending order. Could please kindly tell me the ways to do all of the questions above.
Thank you so much for taking your time to help me.
 
Upvote 0
Hi HaMinh:

How about ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCD
1
22/15/20066/15/20065/20/2006
35/15/2006
45/20/2006
510/15/2006
6
Sheet8


</body></html>
 
Upvote 0
Try this array entered with CTRL + SHIFT + ENTER

=MAX(IF(A1:A100<=E1,A1:A100))

E1 = Target Date 6/15/2006

After entering formula, highlight cell and press F2, then press
CTRL + SHIFT + ENTER

When entered correctly, formula will be enclosed in {}

Hope this helps..
 
Upvote 0
Dear jonmo1 and Yogi Anand,

Thank you so much for taking your time to help me out. I just have a stupid question, why do we have to use CTRL + SHIFT + ENTER? What does it use for and in what case do we need to use it? Also can we just drag it down if we need to use this formula many times?
 
Upvote 0
This formula is for finding the date in the list that is closest to the specified date whether it is earlier or later than the specified date:
Code: 
=INDEX(Datelist, MATCH(MIN(ABS(Datelist-A1)), ABS(Datelist-A1), 0))

where the specified date is in A1 and the list of dates is in the named range Datelist.
 
Upvote 0
This is also an array formula that needs to be entered with Ctrl + Shift + Enter. Yes, it CAN be dragged down as long as the reference to the date list is absolute (not relative).

You can read up on array formulas on this website.
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
273
skyh3ck
skyh3ck
S
  • Question
How to find the closest date after
Replies
3
Views
2K
JoeMo
J
H
  • Question
Copy data from one sheet to another sheet quickly.
Replies
5
Views
383
johnnyL
J
I
  • Question
Unable to change date format
Replies
2
Views
58
Imajica
I
L
  • Solved
How to get Login/Logout Times for users with multiple login/logouts on the multiple days
Replies
3
Views
177
leenvr
L

Forum statistics

Threads
1,214,988
Messages
6,122,620
Members
449,092
Latest member
amyap

Share this page

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