retrieve nearest date

[rodrigje]

Hi all,

Im doing a concatenate where a statement will read..."Cumulative From 5/01/05 to ________" where the _____ is a date chosen from a column where I need it to pick the nearest date to today from that range. Thanks alot for looking at this

 

[acw]

Hi

How you determine the "nearest"? Is it the one with the least number of days difference between the 2 dates (so could be either before or after the date), or the latest date before today.


Tony

 

[barry houdini]

Assuming you want the latest date from the range A1:A100 (which, as Tony implies, is not necessarily the same as the date nearest to today)

="Cumulative From 5/01/05 to "&TEXT(MAX(A1:A100),"d/mm/yy")

 

[rodrigje]

Barryhoudini and acw you both are great! that's EXACTLY what I needed and I just impressed the heck out of the boss! Thank you!!