Forum Discussion
Solved
Finding the earliest date in a range of dates
In one row I have an array of 10 dates spanning a decade e.g 2010 - 2019. 1/4/2010, 6/7/2011, 3/5/2012, 2/3/2013 …..etc I need a formula that will find the earliest of those 10 dates using only t...
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!
