Count dates in next quarter / multiple years

[sparkytech]

I am trying to count multi-year dates in N5:N1500 that occur in the next quarter only. Starting at BA5 in column BA5:BA1500, I have the following typical formula:

=IF(AND(O5="",N5>=TODAY(),N5<>""),(ROUNDUP(MONTH(N5)/3,0)),"")

This checks to see if the date in N5 is greater or equal to today and that O5 does not contain a date, and then displays the quarter of the N5 date in BA5. I also have the following formula to calculate the total cells in column BA that match the next quarter:

=COUNTIF(BA5:BA1500,(ROUNDUP(MONTH(TODAY())/3,0)+1))

The problem I have is since the dates in Column "N" span multiple years, any dates occurring in a quarter are counted for this year, and also future years (i.e: any dates occurring in Q3 are counted for 2021, 2022, 2023, etc.). How can I remedy this? My end goal is to look at N5:N1500 and total any dates occurring in the next quater. Thanks in advance!

 

[Toadstool]

Hi Sparkytech,

Why not just add the year as a part of the BA column?

Sparkytech.xlsx
	N	O	P	Q																																			AZ	BA
3			Count=	3
4	Dates	Oblank																																						Check
5	07-Aug-21																																							2021 3
6	09-Sep-21																																							2021 3
7	01-Aug-21																																							2021 3
8	04-Sep-24																																							2024 3
9	07-Jun-23																																							2023 2
10	12-Feb-21
11	08-Aug-21	Not
12	11-Feb-24																																							2024 1
13	20-Feb-21
14	02-Jun-22																																							2022 2
15	22-Sep-26																																							2026 3
16	19-Apr-22																																							2022 2
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNTIF(BA5:BA1500,(YEAR(TODAY())&" "&ROUNDUP(MONTH(TODAY())/3,0)))
BA5:BA16	BA5	=IF(AND(O5="",N5>=TODAY(),N5<>""),YEAR(N5)&" "&(ROUNDUP(MONTH(N5)/3,0)),"")

 

[sparkytech]

Hi Sparkytech,

Why not just add the year as a part of the BA column?

Sparkytech.xlsx
	N	O	P	Q																																			AZ	BA
3			Count=	3
4	Dates	Oblank																																						Check
5	07-Aug-21																																							2021 3
6	09-Sep-21																																							2021 3
7	01-Aug-21																																							2021 3
8	04-Sep-24																																							2024 3
9	07-Jun-23																																							2023 2
10	12-Feb-21
11	08-Aug-21	Not
12	11-Feb-24																																							2024 1
13	20-Feb-21
14	02-Jun-22																																							2022 2
15	22-Sep-26																																							2026 3
16	19-Apr-22																																							2022 2
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNTIF(BA5:BA1500,(YEAR(TODAY())&" "&ROUNDUP(MONTH(TODAY())/3,0)))
BA5:BA16	BA5	=IF(AND(O5="",N5>=TODAY(),N5<>""),YEAR(N5)&" "&(ROUNDUP(MONTH(N5)/3,0)),"")

Thanks for the suggestion! Will that work when todays date is in Q4, to count Q1 dates of the following year?

 

[Toadstool]

Ah! I missed the count was for next quarter dates.

Here, this should work for you.

Sparkytech.xlsx
	N	O	P	Q																																			AZ	BA
3			Count=	4
4	Dates	Oblank																																						Check
5	07-Oct-21																																							2021 4
6	09-Sep-21																																							2021 3
7	01-Aug-21																																							2021 3
8	04-Sep-22																																							2022 3
9	07-Jun-23																																							2023 2
10	12-Feb-21
11	08-Aug-21	Not
12	11-Nov-21																																							2021 4
13	20-Feb-21
14	01-Jan-23																																							2023 1
15	11-Nov-21																																							2021 4
16	31-Dec-21																																							2021 4
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNTIF(BA5:BA1500,YEAR(TODAY())+INT(MONTH(TODAY())/10)&" "&INDEX({2,2,2,3,3,3,4,4,4,1,1,1},MONTH(TODAY())))
BA5:BA16	BA5	=IF(AND(O5="",N5>=TODAY(),N5<>""),YEAR(N5)&" "&(ROUNDUP(MONTH(N5)/3,0)),"")

 

[Toadstool]

...and here's a test version where cell Q1 has the date for TODAY so you can just overtype it to test for any date.

Sparkytech.xlsx
	N	O	P	Q																																			AZ	BA
1			Today=	7/13/2021
2
3			Count=	4
4	Dates	Oblank																																						Check
5	07-Oct-21																																							2021 4
6	09-Sep-21																																							2021 3
7	01-Aug-21																																							2021 3
8	04-Sep-22																																							2022 3
9	07-Jun-23																																							2023 2
10	12-Feb-21
11	08-Aug-21	Not
12	11-Nov-21																																							2021 4
13	20-Feb-21
14	01-Jan-23																																							2023 1
15	11-Nov-21																																							2021 4
16	31-Dec-21																																							2021 4
Sheet1 (2)

Cell Formulas
Range	Formula
Q1	Q1	=TODAY()
Q3	Q3	=COUNTIF(BA5:BA1500,YEAR($Q$1)+INT(MONTH($Q$1)/10)&" "&INDEX({2,2,2,3,3,3,4,4,4,1,1,1},MONTH($Q$1)))
BA5:BA16	BA5	=IF(AND(O5="",N5>=TODAY(),N5<>""),YEAR(N5)&" "&(ROUNDUP(MONTH(N5)/3,0)),"")