How to find the closest date

[haminh]

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

 

[Dan Waterloo]

http://www.mrexcel.com/tip011.shtml

http://www.mrexcel.com/tip031.shtml

 

[haminh]

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:

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

Thanks so much for your time Dan Waterloo. Could you please tell me how to use this formula if I need to find a earliest date before a specific date?

 

[Dan Waterloo]

I think the formula given earlier by Yogi and jonmo is more suitable for for finding the closest date that is before the specified date:

 {=MAX(IF($A$2:$A$5<=C2,$A$2:$A$5))}

And if you want the closest date after the specified date you would tweak it like this:

 {=MIN(IF($A$2:A$5>=C2,$A$2:$A$5))}

The dollar signs make the reference to this list to be absolute rather than relative and lets you apply the formula to other values in column A by dragging it down (or however you want to copy it).

 

[Drrellik]

This is an old thread, however I have a very similar question, and I did not want to create an entire new post.

My question is this

Excel 2010
	I	J	K	L	M	N
2	Anniversary		Pay Dates
3	2/10/2016		1/9/2016		2/6/2016	vlookup
4	4/3/2016		1/23/2016		1/7/1900	match -1
5	3/6/2016		2/6/2016		#N/A	match 0
6	3/13/2016		2/20/2016		#N/A	match 1
7	3/20/2016		3/5/2016
8	3/27/2016		3/19/2016
Sheet1

Cell Formulas
Range	Formula
M3	=VLOOKUP(I3,$K$3:$K$28,1)
M4	=MATCH(I4,$K$3:$K$28,1)
M5	=MATCH(I5,$K$3:$K$28,0)
M6	=MATCH(I6,$K$3:$K$28,-1)
I3	=DATE(YEAR($E$2)+1,MONTH(C3),DAY(C3))
I4	=DATE(YEAR($E$2)+1,MONTH(C4),DAY(C4))
I5	=DATE(YEAR($E$2)+1,MONTH(C5),DAY(C5))
I6	=DATE(YEAR($E$2)+1,MONTH(C6),DAY(C6))
I7	=DATE(YEAR($E$2)+1,MONTH(C7),DAY(C7))
I8	=DATE(YEAR($E$2)+1,MONTH(C8),DAY(C8))

The dates in the Anniversary column are created with a formula based on the current year in a helper cell and the month and day come from the employee hire date.
use / lose vacation carries until the pay period following your anniversary. how can I find the next date, and not the previous date.

M3 result should be 2/20/16
M5 and M6 should be 3/19/16

hope this makes sense.

Don

 

[Jym396]

THAT is EXACTLY what I was trying to do...only I wanted to find the next date..so I used =min and the >=. The only thing is...is that I thought I thoroughly understood the =if function..and apparently I do not. Also..in the past I never used the Ctrl-Shift-Enter (CSE) and my formulas seemed to work? Don't know.

Thanks,
Jim

 

[Drrellik]

Jim,

I eventually created a hidden helper column that contained all the pay dates ( every other Friday) and then used this #=IF(VLOOKUP(DATE(YEAR($K$2),MONTH(E4),DAY(E4)+13),$O$4:$O$460,1)<$J$2,VLOOKUP(DATE(YEAR($K$2)+1,MONTH(E4),DAY(E4)+13),$O$4:$O$460,1),(VLOOKUP(DATE(YEAR($K$2),MONTH(E4),DAY(E4)+13),$O$4:$O$460,1)))#

I used the vlookup in an IF statement and used +13 days so I could always find the next available date and not the one that had just pasted.

Don

 

[Aladin Akyurek]

THAT is EXACTLY what I was trying to do...only I wanted to find the next date..so I used =min and the >=. The only thing is...is that I thought I thoroughly understood the =if function..and apparently I do not. Also..in the past I never used the Ctrl-Shift-Enter (CSE) and my formulas seemed to work? Don't know.

Thanks,
Jim

Care to post a sample along with the expected results without reference to any formula?