Forum Discussion
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!
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)
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...
- 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?
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)
