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!
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)- 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?
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.
- Thank you very much. I forgot to mention that every second cell is a number not a date. Will that mess thing up?
