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!
As variant
=LET(
dates, $A$1:$J$1,
minDate, MIN( MONTH(dates)*100+DAY(dates) ),
MOD(minDate, 100) & " " & TEXT( 29*INT(minDate/100), "mmm" )
)- This for me results in a #NAME? error. Here is what I have entered from your suggestion:
10/06/2010 05/05/2011 29/05/2012 07/06/2013 13/05/2014 25/05/2015 17/05/2016 09/05/2017 15/05/2018 11/05/2019
=LET( dates, $A$1:$J$1, minDate, MIN( MONTH(dates)*100+DAY(dates) ), MOD(minDate, 100) & " " & TEXT( 29*INT(minDate/100), "mmm" ))Alternatively that could be
=AGGREGATE(15,6, DAY(dates)/(MONTH(dates)=MIN(MONTH(dates))),1) & " " & TEXT(29*MIN(MONTH(dates)), "mmm" )where range A1:J1 is named as dates
When
=MOD( MIN( MONTH($A$1:$J$1)*100+DAY($A$1:$J$1) ), 100) & " " & TEXT( 29*INT(MIN( MONTH($A$1:$J$1)*100+DAY($A$1:$J$1) )/100), "mmm" )shall work. Please check attached file.
