Google Sheets: Compare two dates by Year and count if same year-CodePudding

I have the following data in google sheets (or excel). I want to compare the dates in column B and C to determine if they occurred in the same year, without changing the format of the dates or adding the example column D below. Then, if they did occur in the same year, I want to count how many for that specific year.

A   B           C            D
1   7/7/2020    11/18/2021  (No)
2   8/25/2021   11/17/2021  (Yes)
3   5/27/2021   10/27/2021  (Yes)
4   12/8/2020   10/27/2021  (No)
5   3/27/2019   10/18/2021  (No)
6   1/25/2018   6/30/2018   (Yes)

What I am trying to show/count: Year - Count 2021 - 2 2020 - 0 2019 - 0 2018 - 1

Thank you in advance for your help!

CodePudding user response：

Try

=query(query(A1:B6,"select year(A) where year(A)=year(B)"),"select Col1,count(Col1) group by Col1")

CodePudding user response：

try:

=INDEX(IFNA(REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}")=REGEXEXTRACT(TO_TEXT(C1:C), "\d{4}")))

and then:

=INDEX(QUERY(IFNA({
 REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}"), ( 
 REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}")=
 REGEXEXTRACT(TO_TEXT(C1:C), "\d{4}"))*1}), 
 "select Col1,sum(Col2) where Col1 is not null 
  group by Col1 label sum(Col2)''"))