Forum Discussion
Finding the earliest date in a range of dates
Clive_Rabson You're very welcome. As an afterthought, if you want to avoid the need for a helper column and/or Name Manager, the same method(s) can be combined with INDEX/MATCH to create a single worksheet formula:
=INDEX(A2:J2, MATCH(TEXT(MIN(IF(ISODD(COLUMN(A2:J2)),--TEXT(A2:J2,"mmddyyyy"))),"00000000"), TEXT(A2:J2,"mmddyyyy"), 0))Note: this is an array formula and must be input with Ctrl+Shift+Enter in older versions of Excel.
Cheers!
You could set the anniversary of each date in the list
= LET(
anniversary, DATE(YEAR(TODAY()), MONTH(dateList), DAY(dateList)),
MIN(anniversary)
)Then again, if you were working from a string of dates such as that in the question things would look different
The only issue with YEAR(TODAY()) if one of the dates is 29 Feb of leap year.
- You just said:
Clive_Rabson
E1 is 5th column after A1, are you sure?
I thought that was every 4th. But I am getting confused now. But it should be A1, E1, I1 etc Didn't think of that one!
= LET( dateArr,--TEXTSPLIT(dateString,"/",","), sorted, SORT(dateArr,{2,1},{1,1}), earliest, DATEVALUE(TEXTJOIN("/",,TAKE(sorted, 1))), earliest )- I cannot use the Function LET or TEXTSPLIT in my version of Excel, and my 2015 Mac will not allow me to upgrade MS Office to a higher version
That lets me off the hook! I do not use or support obsolete versions of Excel
.But then, I described the A1 referencing systems as an abomination that has no place in any rational computational environment!
