Is there a funtion for comparing dates?

[Scott82]

Hi all

I was hoping for a function to return the nearest date to todays date out of a range of dates.
Do you know of a function for this?

 

[baitmaster]

dates are actually numerical values, formatted as dates, so you are looking for the value in a list, where the differnce between that value and today's value is minimal

Try
=today()-MIN(ABS([your range]-today()))
entered with shift + ctrl + enter

 

[CharlesChuckieCharles]

Not sure that its entirely accrurate

=$E$1-MIN(ABS($B$3:$B$37-$E$1))

e1=30/06/2010

Range is dates from 01/06/2010 step 3 days ( next date 04/06/2010 etc)

result 29/06/2010
which is not a date in the list

(01/07/2010 was and it is the Nearest date )
which abs(1) difference

so 30/06/2010- 1 = 29/06/2010

But it's not the nearest in the list

 

[Scott82]

Not sure that its entirely accrurate

Ive tryed the formula amd its coming up with a random date that is not in the list too!

I am only interested in dates in the future also (incase that makes a difference?)

Thank you

 

[Marcelo Branco]

Hi,

Assuming your dates in A1:A5 and Today in D1 maybe this

=INDEX($A$1:$A$5,MATCH(MIN(ABS($A$1:$A$5-$D$1)),ABS($A$1:$A$5-$D$1),0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

Format as Date

HTH

M.

 

[CharlesChuckieCharles]

That works great Marcelo

 

[Marcelo Branco]

That works great Marcelo

Glad to help and tks for the feedback

M.

 

[baitmaster]

the formula I put was part right, but it subtracts rather than adds in some circumstances

29/6 was not in the list because it was 30/6 - 1, it should have been 30/6+1 = 1/7

{=MIN(ABS([your range]-today()))} picks up the smallest difference, it just doesnt note whether that relates to a bigger or smaller value than today. I subtracted it from todays date because my result was looking at a date in the past, which distracted me

If they are all in the future, then {=MIN(ABS([your range]-today()))+today()} should work

 

[Scott82]

Thanks Guys

Will these both work only in an array?
Would they still work looking at scattered cells?

 

[Marcelo Branco]

Thanks Guys

Will these both work only in an array?
Would they still work looking at scattered cells?

If you dont want an array-formula, you can create a helper column, say X1:X5 for my example, with the formula in X1
=ABS(A1-$D$1)
copy down

and change to a regular formula
=INDEX($A$1:$A$5, MATCH(MIN($X$1:$X$5),$X$1:$X$5,0))

With scattered cells in the same column, yes. But with scattered cells in different columns...another problem...

M.