Conditional formatting if date falls within last 4 calendar quarters

[amphead]

I'm trying to add a formula using conditional formatting to see if a cell(G10) falls within the previous 4 calendar quarters of a date in another cell(M2). I've not been able to find a way to make it work. Any suggestions?

I'm using MS 365 Excel

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1												Service Performed
2		Instrument Rack										Date	6/25/2024			2	Quarter		2024
3		Source	Description			Freq.	Date Performed
4		1	Clean Instrument Rack			Q	6/25/2024
5		1	Visual Inspection			Q	6/25/2024
6		Air Supply
7		Source	Description			Freq.	Date Performed
8		1	Inspect inlet pressure gauge			Q	6/25/2024
9		1	Relieve trapped water from filter cup			Q	6/25/2024
10		1	Change Coalescing Filter			A	1/1/2022
11
Service Checklist

Cell Formulas
Range	Formula
P2	P2	=ROUNDUP(MONTH(M2)/3,0)
S2	S2	=YEAR(M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G9:I9	Expression	=(ROUNDUP(MONTH(G9)/3,0))<>P2	text	NO
G8:I8	Expression	=(ROUNDUP(MONTH(G8)/3,0))<>P2	text	NO
G5:I5	Expression	=(ROUNDUP(MONTH(G5)/3,0))<>P2	text	NO
G4:I4	Expression	=(ROUNDUP(MONTH(G4)/3,0))<>P2	text	NO

 

[etaf]

can you give an example of 4 calendar quarters - isnt that a YEAR ???

so M2 = 25/6/24

what would be the start date of the 4th calendar quarter - IE how far back do you go with that date

25/6/23 OR ??????

 

[amphead]

Yes, it's a year. However, I can just use 365 days since the date in M2 may not be the first day of the quarter. I'm trying to make the cell turn yellow if the task in C10 is due the same quarter as listed in M2. The letters in F9 and F10 are for quarterly and annual. I was able to get the conditional formatting to work for cell G9 quarterly, but not G10 for annual.

1st quarter = 1/1 - 3/31
2nd quarter = 4/1 - 6/30
3rd quarter = 7/1 - 9/30
4th quarter = 10/1 - 12/31

Using the date of 6/25/24. It's in 2nd quarter, so I want to check if the date in cell G10 is older than the beginning of quarter 3 in 2023 or 7/1/23.

 

[amphead]

I made a mistake on my response earlier. I meant to say I can't use 365 days. Just using the past year may not line up with the calendar quarters.

 

[etaf]

thanks still not following sorry
go back 4 quarters

25/6/24 - go back 4 quarters - so that in Q2 , go back 4 - Q1,Q4,Q3,Q2 , so 1 April -
Then look at G10 and see if that date , falls within that date - 1/oct/23

which is NO

BUT you say

so I want to check if the date in cell G10 is older than the beginning of quarter 3 in 2023 or 7/1/23.

OLDER and 3 Qtrs
so go back 3 qtrs and Q1,Q4, Q3 , 1st July

BUT then in the Original post you say

cell(G10) falls within the previous 4 calendar quarters of a date in another cell(M2). I

SO falls within - OR older
Q3 - counting the current quarter as 1 or Q4 go back for qtrs

 

[amphead]

Sorry for the confusion. I'll see if I can clarify.

25/6/24 - go back 4 quarters - so that in Q2 , go back 4 - Q1,Q4,Q3,Q2 , so 1 April - I made a mistake, it should go back 3 quarters, so July 1.
Then look at G10 and see if that date , falls within that date - 1/oct/23 - I need to check if G10 is between July 1, 2023 and the date listed in M2

which is NO

BUT you say

so I want to check if the date in cell G10 is older than the beginning of quarter 3 in 2023 or 7/1/23.

OLDER and 3 Qtrs
so go back 3 qtrs and Q1,Q4, Q3 , 1st July - Yes, it should go back 3 quarters, so 1st July. It looks like I said "falls within" and then "older than". I didn't realize I did that when I typed the response. I need it to color the cell yellow if it doesn't fall with the dates (July 1, 2023-June 25,2024) or is older than the 1st July. Would one of those methods work better or be easier?

BUT then in the Original post you say

cell(G10) falls within the previous 4 calendar quarters of a date in another cell(M2). I

SO falls within - OR older - Sorry for the confusion on using both of those terms in my question. I'm guessing it may be easier to go older than the previous 3 quarters.
Q3 - counting the current quarter as 1 or Q4 go back for qtrs - If counting the current quarter listed in M2, then go back 3 additional quarters. Using the date of 6/25/24 in my example: its Q2 2024, so July 1, 2023.

 

[amphead]

I was able to come up with a solution to my problem. I setup the conditional formatting with this formula: =(DATEDIF(G10, M2, "m")/3)>3.75. I can adjust the 3.75 number to look back more or less quarters if needed in the future.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1												Service Performed
2		Instrument Rack										Date	6/30/2024			2	Quarter		2024
3		Source	Description			Freq.	Date Performed
4		1	Clean Instrument Rack			Q	6/25/2024
5		1	Visual Inspection			Q	6/25/2024
6		Air Supply
7		Source	Description			Freq.	Date Performed
8		1	Inspect inlet pressure gauge			Q	6/25/2024
9		1	Relieve trapped water from filter cup			Q	6/25/2024
10		1	Change Coalescing Filter			A	6/30/2023
11
Service Checklist

Cell Formulas
Range	Formula
P2	P2	=ROUNDUP(MONTH(M2)/3,0)
S2	S2	=YEAR(M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G10:I10	Expression	=(DATEDIF(G10, M2, "m")/3)>3.75	text	NO
G9:I9	Expression	=(ROUNDUP(MONTH(G9)/3,0))<>P2	text	NO
G8:I8	Expression	=(ROUNDUP(MONTH(G8)/3,0))<>P2	text	NO
G5:I5	Expression	=(ROUNDUP(MONTH(G5)/3,0))<>P2	text	NO
G4:I4	Expression	=(ROUNDUP(MONTH(G4)/3,0))<>P2	text	NO

 

[etaf]

glad you managed to sort out - good solution, i was a long way from that