Find The Earliest Date In Two Specific Years

[Dtex20]

Hi Guys,

Lets say you have 20 Dates, 10 for each year: 2018, 2019. You also have two ID's.

I'm trying to find the earliest date in 2018 and the earliest date in 2019, in which each ID appear.

MIN, MAX wont work, as the dates can be in the future or in the past. The data also isn't sorted and the large data set will take too long to do manually. I'm looking for a formula.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID 1[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 2[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 1[/TD]
[TD]01/05/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 2[/TD]
[TD]01/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 1[/TD]
[TD]01/01/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 2[/TD]
[TD]01/07/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 1[/TD]
[TD]01/05/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID 2[/TD]
[TD]01/08/2019[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

ID 1 - 01/01/2018 - 01/05/2019
ID 2 - 01/03/2018 - 01/08/2019

Above would be the correct answer, for the table example.

Thanks,

 

[Fluff]

How about

Book1
	A	B	C
1	ID 1	01/01/2017
2	ID 2	01/01/2017
3	ID 1	01/05/2018
4	ID 2	01/03/2018
5	ID 1	01/01/2018
6	ID 2	01/07/2018
7	ID 1	01/05/2019
8	ID 2	01/08/2019
9
10		2018	2019
11	ID 1	01/01/2018	01/05/2019
12	ID 2	01/03/2018	01/08/2019
Log

Cell Formulas
Range	Formula
B11	{=MIN(IF(($A$1:$A$8=$A11)*(YEAR($B$1:$B$8)=B$10),$B$1:$B$8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dtex20]

How about

	A	B	C
ID 1
ID 2
ID 1
ID 2
ID 1
ID 2
ID 1
ID 2
ID 1
ID 2

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]01/01/2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01/01/2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/05/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]01/03/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]01/01/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]01/07/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]01/05/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]01/08/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]01/05/2019[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]01/08/2019[/TD]

</tbody>

Log​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]{=MIN(IF(($A$1:$A$8=$A11)*(YEAR($B$1:$B$8)=B$10),$B$1:$B$8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

That is perfect mate! Thank you very much

 

[Fluff]

You're welcome & thanks for the feedback