Forum Discussion
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?
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!
31 Replies
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)- 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)}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...
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
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
The only issue with YEAR(TODAY()) if one of the dates is 29 Feb of leap year.
- You just said:
Clive_Rabson
E1 is 5th column after A1, are you sure?
I thought that was every 4th. But I am getting confused now. But it should be A1, E1, I1 etc
= LET( dateArr,--TEXTSPLIT(dateString,"/",","), sorted, SORT(dateArr,{2,1},{1,1}), earliest, DATEVALUE(TEXTJOIN("/",,TAKE(sorted, 1))), earliest )- 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
- Please provide some data and expected result in table format.
