# Find earliest date of year in list

#### mattbnorris

##### New Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Tetra201

##### MrExcel MVP
If you have Excel 365, try
Excel Formula:
=MINIFS(A2:A10,A2:A10,">="&DATE(UNIQUE(YEAR(A2:A10)),1,1),A2:A10,"<"&DATE(UNIQUE(YEAR(A2:A10)),12,32))

#### Rick Rothstein

##### MrExcel MVP
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...
Excel Formula:
=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

##### Well-known Member
try Aggregate
T202101b.xlsm
ABCD
115-Jan-21201910-May-19
28-Jan-21202030-Mar-20
36-Jan-21202115-Jan-21
430-Mar-20
4b
Cell Formulas
RangeFormula
D1:D3D1=AGGREGATE(15,6,\$A\$1:\$A\$8/(YEAR(A1:A8)=C1),1)

#### sandy666

##### Banned - Rules violations

 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

Power Query:
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

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

You are welcome

Replies
2
Views
62
Replies
2
Views
52
Replies
5
Views
110
Replies
2
Views
104
Replies
5
Views
76

1,127,801
Messages
5,626,965
Members
416,212
Latest member
Ifemiide

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back