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...

Excel on Mac

djclements
Jun 09, 2024
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!
MinByMonthDay(OddColumns)v2.xlsx10 KB

PeterBartholomew1

Silver Contributor

Jun 08, 2024

= LET(
    dateArr,--TEXTSPLIT(dateString,"/",","),
    sorted,   SORT(dateArr,{2,1},{1,1}),
    earliest, DATEVALUE(TEXTJOIN("/",,TAKE(sorted, 1))),
    earliest
  )

Clive_Rabson

Brass Contributor

Jun 08, 2024

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

PeterBartholomew1
Silver Contributor
Jun 08, 2024
Clive_Rabson
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!

Forum Discussion

Finding the earliest date in a range of dates

Resources