Extract Dates from 2 columns

[Cowichandave]

The attachment has 2 columns with dates. One is date joined, the other is date last active. The file contains 30 items First of all I want to extract only those active this year. This formula selects 18 active this year

=COUNTIFS($D$5:$D$34,">="&DATE(2023,1,1),$D$5:$D$34,"<="&DATE(2023,12,31))

Out of the 18 active I want the refine the formula to show who joined in 2021 and was active this year. The answer should be 8.
How do I do that?

Rolling Report TEMPLATE.xlsm
	A	B	C	D
4	Number	ID	Joined	Active
5	1	MLS02	2023-01-16	2023-11-10
6	2	ERM02	2023-05-03	2023-11-08
7	3	HOM06	2023-04-20	2023-11-08
8	4	JON03	2023-01-23	2023-11-08
9	5	AUR06	2022-07-04	2023-11-10
10	6	APE07	2022-06-20	2023-11-09
11	7	BRO57	2022-06-27	2023-10-31
12	8	DES18	2022-06-28	2023-10-31
13	9	HEL04	2022-06-02	2023-10-31
14	10	LED62	2022-06-29	2023-10-31
15	11	CAN107	2021-09-08	2023-11-10
16	12	ESC02	2021-06-08	2023-11-09
17	13	EAR03	2021-01-07	2023-11-09
18	14	MJL01	2021-02-04	2023-11-09
19	15	OBS03	2021-04-15	2023-11-08
20	16	CIV03	2021-05-28	2023-11-06
21	17	MEA06	2021-10-25	2023-11-06
22	18	RIV22	2021-08-16	2023-11-06
23	19	BEC01	2022-10-03	2022-12-30
24	20	PRI09	2022-08-25	2022-12-16
25	21	GofBC10	2021-07-28	2022-12-13
26	22	MRG03	2021-11-03	2022-12-12
27	23	AMA08	2021-02-19	2022-11-28
28	24	AKA02	2021-11-09	2022-11-08
29	25	ATB04	2017-03-10	2021-12-31
30	26	RCM177	2011-09-21	2021-12-31
31	27	RDI02	2020-07-08	2021-12-31
32	28	SUM10	2006-10-23	2021-12-31
33	29	DRA18	2018-08-16	2021-12-24
34	30	TT001	2004-08-16	2021-12-23
Sheet2

 

[alansidman]

A power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Joined", type date}, {"Active", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentYear([Active])),
    #"Extracted Year" = Table.TransformColumns(#"Filtered Rows",{{"Joined", Date.Year, Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Extracted Year", each ([Joined] = 2021))
in
    #"Filtered Rows1"

 

[Peter_SSs]

A couple of formula options for each part.

23 11 19.xlsm
	C	D	E	F	G
4	Joined	Active		Active this year	Active this year & Joined 2021
5	2023-01-16	2023-11-10		18	8
6	2023-05-03	2023-11-08		18	8
7	2023-04-20	2023-11-08
8	2023-01-23	2023-11-08
9	2022-07-04	2023-11-10
10	2022-06-20	2023-11-09
11	2022-06-27	2023-10-31
12	2022-06-28	2023-10-31
13	2022-06-02	2023-10-31
14	2022-06-29	2023-10-31
15	2021-09-08	2023-11-10
16	2021-06-08	2023-11-09
17	2021-01-07	2023-11-09
18	2021-02-04	2023-11-09
19	2021-04-15	2023-11-08
20	2021-05-28	2023-11-06
21	2021-10-25	2023-11-06
22	2021-08-16	2023-11-06
23	2022-10-03	2022-12-30
24	2022-08-25	2022-12-16
25	2021-07-28	2022-12-13
26	2021-11-03	2022-12-12
27	2021-02-19	2022-11-28
28	2021-11-09	2022-11-08
29	2017-03-10	2021-12-31
30	2011-09-21	2021-12-31
31	2020-07-08	2021-12-31
32	2006-10-23	2021-12-31
33	2018-08-16	2021-12-24
34	2004-08-16	2021-12-23
Counts

Cell Formulas
Range	Formula
F5	F5	=COUNTIFS($D$5:$D$34,">="&DATE(2023,1,1),$D$5:$D$34,"<="&DATE(2023,12,31))
G5	G5	=COUNTIFS($D$5:$D$34,">="&DATE(2023,1,1),$D$5:$D$34,"<="&DATE(2023,12,31),$C$5:$C$34,">="&DATE(2021,1,1),$C$5:$C$34,"<="&DATE(2021,12,31))
F6	F6	=SUMPRODUCT(--(YEAR($D$5:$D$34)=2023))
G6	G6	=SUMPRODUCT((YEAR($D$5:$D$34)=2023)*(YEAR($C$5:$C$34)=2021))