SOLVED

# Finding the earliest date in a range of dates

Brass Contributor

# 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 the day and the month while ignoring the year
• Obviously if it’s searched with MIN(A1:J1) for the earliest date then it would produce the 2010 (1/4/2010) date but the earliest date based on only day/month is 2/3/2013.
how can my search ignore the year?

31 Replies

# Re: Finding the earliest date in a range of dates

Please provide some data and expected result in table format.

# Re: Finding the earliest date in a range of dates

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

# Re: Finding the earliest date in a range of dates

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

# Re: Finding the earliest date in a range of dates

The only issue with YEAR(TODAY()) if one of the dates is 29 Feb of leap year.

# Re: Finding the earliest date in a range of dates

As variant

``````=LET(
dates,  \$A\$1:\$J\$1,
minDate,  MIN( MONTH(dates)*100+DAY(dates) ),
MOD(minDate, 100)  & " " & TEXT( 29*INT(minDate/100), "mmm" )
)``````

# Re: Finding the earliest date in a range of dates

@Clive_Rabson Quite simply:

``=@SORTBY(A1:J1, MONTH(A1:J1), 1, DAY(A1:J1), 1)``

Ties can also be settled by adding a third sort field by year:

``=@SORTBY(A1:J1, MONTH(A1:J1), 1, DAY(A1:J1), 1, YEAR(A1:J1), 1)``

EDIT: A simplified version of which would be:

``=@SORTBY(A1:J1, TEXT(A1:J1, "mmddyyyy"), 1)``

# Re: Finding the earliest date in a range of dates

Didn't think of that one!

# Re: Finding the earliest date in a range of dates

Thanks. All of these solutions result in a #NAME? error.
This is my understanding of what you are suggesting:
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 #NAME? {SORTBY(A2:J2,MONTH(A2:J2),1,DAY(A2:J2),1)}

# Re: Finding the earliest date in a range of dates

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" ))

# Re: Finding the earliest date in a range of dates

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

# Re: Finding the earliest date in a range of dates

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!

# Re: Finding the earliest date in a range of 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.

# Re: Finding the earliest date in a range of dates

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

# Re: Finding the earliest date in a range of dates

@Clive_Rabson The SORTBY function is only available in Excel 2021 and newer. Another approach for older versions of Excel could be:

(1) Select cell K2, then open Name Manager and define a new name called MinByMonthDay with the following formula:

``=TEXT(MIN(--TEXT(Sheet1!\$A2:\$J2,"mmddyyyy")),"00000000")``

Note: adjust the sheet name as per your workbook.

(2) Then, input the following formula directly in cell K2:

``=DATE(RIGHT(MinByMonthDay,4),LEFT(MinByMonthDay,2),MID(MinByMonthDay,3,2))``

The formula can then be copied down as needed. Please see the attached workbook...

# Re: Finding the earliest date in a range of dates

Thank you DJ. Do you think this will also work if I pick out alternate dates or for example every fourth date in the range?

# Re: Finding the earliest date in a range of dates

@Clive_Rabson To pick out every fourth date, you could try something along these lines for the MinByMonthDay formula definition:

``=TEXT(MIN(IF(NOT(MOD(COLUMN(Sheet1!\$A2:\$J2),4)),--TEXT(Sheet1!\$A2:\$J2,"mmddyyyy"))),"00000000")``

To pick out every second date instead, change the divisor argument in the MOD function from 4 to 2. Also, if your date range starts in any column other than A, you will need to adjust the COLUMN numbers accordingly, e.g. COLUMN(Sheet1!\$C1:\$L1)-2.

# Re: Finding the earliest date in a range of dates

Based on recent discussion modified formula a bit

``=AGGREGATE(15,6, \$A2:\$J2 /( (MONTH(\$A2:\$J2)*100+DAY(\$A2:\$J2)) = AGGREGATE(15,6,( MONTH(\$A2:\$J2)*100+DAY(\$A2:\$J2) )/NOT( MOD(COLUMN(\$A2:\$J2),L\$1) ),1)  ),1)``

# Re: Finding the earliest date in a range of dates

Sergei. Ochen Spasiba.
That is very kind of you to take so much trouble.
Every second cell is a number and not a date. How will that affect your proposed solution?

# Re: Finding the earliest date in a range of dates

Thank you very much. I forgot to mention that every second cell is a number not a date. Will that mess thing up?
1 best response

Accepted Solutions
best response confirmed by Clive_Rabson (Brass Contributor)
Solution

# Re: Finding the earliest date in a range of dates

@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!