Forum Discussion

Clive_Rabson's avatar
Clive_Rabson
Brass Contributor
Jun 08, 2024
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...
Excel on Mac
  • djclements's avatar
    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!

Clive_Rabson's avatar
Clive_Rabson
Brass Contributor
Jun 09, 2024

djclements 

Just when you think you're there, you find another bug. The attached shows that in Row 4 there are plenty of real (First Dates) dates. But the formula shows a blank (or 00/01/00). Using IFERROR does not fix it.
Any idea?

The current formula is:

IFERROR(AGGREGATE(15,6, $D4:$AQ4 /( (MONTH($D4:$AQ4)*100+DAY($D4:$AQ4)) = AGGREGATE(15,6,( MONTH($D4:$AQ4)*100+DAY($D4:$AQ4) )/NOT( MOD(COLUMN($D4:$AQ4),4) ),1)  ),1),"")
djclements's avatar
djclements
Silver Contributor
Jun 09, 2024

Clive_Rabson Thank you for providing the sample file. With the headers present, the formula can be simplified to only include the "FirstDate" columns that are greater than 0:

 

=INDEX(D3:AQ3,MATCH(TEXT(MIN(IF(($D$2:$AQ$2=AR$2)*(D3:AQ3>0),--TEXT(D3:AQ3,"mmddyyyy"))),"00000000"),TEXT(D3:AQ3,"mmddyyyy"),0))

 

See attached...

Resources