Forum Discussion

Clive_Rabson's avatar
Clive_Rabson
Brass Contributor
Jun 07, 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 08, 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
Thanks.
I need to only look at every 4th cell. E.g:
A1,E1, I1, M1, Q1, U1 etc etc
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 09, 2024

Clive_Rabson 

Every 4th is

=AGGREGATE(
   15, 6,
   $A2:$J2 /
   ( (MONTH($A2:$J2)*100+DAY($A2:$J2)) =
     AGGREGATE(
        15, 6,
        ( MONTH($A2:$J2)*100+DAY($A2:$J2) ) /
         ( MOD(COLUMN($A2:$J2)-COLUMN($A2),4) = 0),  1 )
   ), 1 )

In above is previous range, you may expand as needed.

Resources