Find earliest date of year in list

[mattbnorris]

Hello!

I've got a list of dates that span across multiple years. Is there a formula I can use to find the earliest date available in each year?

E.g. from the dates below I would want the formula to return 06/01/2021, 30/03/2020 and 10/05/2019

Dates

15/01/2021

08/01/2021

07/01/2021

06/01/2021

04/12/2020

15/09/2020

30/03/2020

09/09/2019

10/05/2019

Any help would be greatly appreciated!

Thanks

 

[Tetra201]

If you have Excel 365, try

=MINIFS(A2:A10,A2:A10,">="&DATE(UNIQUE(YEAR(A2:A10)),1,1),A2:A10,"<"&DATE(UNIQUE(YEAR(A2:A10)),12,32))

 

[Rick Rothstein]

Assuming your example dates themselves are in cells A2:A10 and your years to test for are in B2 (2019), B3 (2020) and B4 (2021), give this array-entered** formula a try...

=MIN(IF(YEAR(A$2:A$10)=B2,A$2:A$10))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Dave Patton]

try Aggregate

T202101b.xlsm
	A	B	C	D
1	15-Jan-21		2019	10-May-19
2	8-Jan-21		2020	30-Mar-20
3	6-Jan-21		2021	15-Jan-21
4	30-Mar-20
4b

Cell Formulas
Range	Formula
D1:D3	D1	=AGGREGATE(15,6,$A$1:$A$8/(YEAR(A1:A8)=C1),1)

 

[sandy666]

Dates		Year	Earliest
15/01/2021		2021	06/01/2021
08/01/2021		2020	30/03/2020
07/01/2021		2019	10/05/2019
06/01/2021
04/12/2020
15/09/2020
30/03/2020
09/09/2019
10/05/2019

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    Year = Table.AddColumn(Source, "Year", each Date.Year([Dates]), Int64.Type),
    Group = Table.Group(Year, {"Year"}, {{"Earliest", each List.Min([Dates]), type datetime}}),
    Type = Table.TransformColumnTypes(Group,{{"Earliest", type date}})
in
    Type

 

[mattbnorris]

All very clever ways of doing it! Thanks for the help

 

[sandy666]

You are welcome