Conditional Formatting on Quarterly Dates

[94mustang]

Hello Excel Experts,

I need help with conditional formatting to highlight the appropriate quarters of the actual audit date each year. I have two worksheets shown below. The first worksheet is the tracking visual and the other is when the actual dates of audits occur.

First, the Start Month and Year are selectable, so the header dates highlighted in grey will move. The Display is also selectable, but I have the Monthly matrix working perfectly. I am wanting the actual audit dates to show up in the appropriate quarter regardless of the Start Month. For example, let's say an audit occurred on Feb 15, 2023, and the Start Month is Jan 2023. I would want the "Q1"(cell: E9) for Plant 4 to be highlighted based on the quarter in which the audit occurred. Also, if the Start Month is March, I would still want cell: E9 to be highlighted. How can I set up the conditional formatting so that regardless of the Start Month, the actual audit dates will be highlighted in the matrix on the appropriate quarter? Thanks in advance for any advice on how to accomplish this task.

Audit Tracking Visual.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1
2
3		Start Month	Jan
4		Start Year	2023
5		Start Date:	01/01/23
6		Display	Quarterly
7				1	Jan-23	Apr-23	Jul-23	Oct-23	Jan-24	Apr-24	Jul-24	Oct-24	Jan-25	Apr-25	Jul-25	Oct-25	Jan-26	Apr-26	Jul-26	Oct-26	Jan-27	Apr-27	Jul-27	Oct-27	Jan-28	Apr-28	Jul-28	Oct-28	Jan-29	Apr-29	Jul-29	Oct-29	Jan-30	Apr-30	Jul-30	Oct-30	Jan-31	Apr-31	Jul-31	Oct-31	Jan-32	Apr-32	Jul-32	Oct-32	Jan-33	Apr-33	Jul-33	Oct-33	Jan-34	Apr-34	Jul-34	Oct-34	Jan-35	Apr-35
8	Division	Plant #	Cert Date		Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2
9	Residential	4	02/15/23
10	Residential	6	11/25/23
11	Residential	10	03/12/23
12	Residential	67
13	Residential	80
14	Residential	81
Audit Tracking Visual

Cell Formulas
Range	Formula
C5	C5	=DATE(C4,D7,1)
D7	D7	=IF(C3="Jan",1,IF(C3="Feb",2,IF(C3="Mar",3,IF(C3="Apr",4,IF(C3="May",5,IF(C3="Jun",6,IF(C3="Jul",7,IF(C3="Aug",8,IF(C3="Sep",9,IF(C3="Oct",10,IF(C3="Nov",11,IF(C3="Dec",12,1))))))))))))
E7	E7	=timeline_start
F7:BB7	F7	=IF(display_category="Quarterly",EDATE(E7,3),IF(display_category="Monthly",EDATE(E7,1),WORKDAY.INTL(E7,1,"0000011")))
E8:BB8	E8	=IF(display_category="Quarterly","Q"&CHOOSE(MONTH(E7),1,1,1,2,2,2,3,3,3,4,4,4),IF(display_category="Monthly",LEFT(TEXT(E7,"mmm"),1),IF(display_category="Weekly",WEEKNUM(E7,21),(LEFT(TEXT(E7,"ddd"),1)))))
C9:C14	C9	=IF('Actual Audit Dates'!C2="","",IFERROR(VLOOKUP(B9,'Actual Audit Dates'!B1:C51,2,FALSE),""))

Named Ranges
Name	Refers To	Cells
display_category	='Audit Tracking Visual'!$C$6	E7:BB8
start_date	='Audit Tracking Visual'!$C$5	E7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E9:BS59	Expression	=OR(AND(OR(MONTH('Actual Audit Dates'!$C2)=1,MONTH('Actual Audit Dates'!$C2)=2,MONTH('Actual Audit Dates'!$C2)=3),OR(MONTH(E$7)=1,MONTH(E$7)=2,MONTH(E$7)=3),OR(E$8="Q1",E$8="Q3")), AND(OR(MONTH('Actual Audit Dates'!$C2)=4,MONTH('Actual Audit Dates'!$C2)=5,MONTH('Actual Audit Dates'!$C2)=6),OR(MONTH(E$7)=4,MONTH(E$7)=5,MONTH(E$7)=6),OR(E$8="Q2",E$8="Q4")), AND(OR(MONTH('Actual Audit Dates'!$C2)=7,MONTH('Actual Audit Dates'!$C2)=8,MONTH('Actual Audit Dates'!$C2)=9),OR(MONTH(E$7)=7,MONTH(E$7)=8,MONTH(E$7)=9),OR(E$8="Q1",E$8="Q3")), AND(OR(MONTH('Actual Audit Dates'!$C2)=10,MONTH('Actual Audit Dates'!$C2)=11,MONTH('Actual Audit Dates'!$C2)=12),OR(MONTH(E$7)=10,MONTH(E$7)=11,MONTH(E$7)=12),OR(E$8="Q2",E$8="Q4")))	text	NO
E9:BS59	Expression	=OR(AND(MOD(YEAR(E$7),4)=3,E$8="Q1", OR(MONTH(E$7)=1,MONTH(E$7)=2,MONTH(E$7)=3),MOD(YEAR($C9),4)=3,OR(MONTH($C9)=1,MONTH($C9)=2,MONTH($C9)=3)),AND(MOD(YEAR(E$7),4)=3,E$8="Q2", OR(MONTH(E$7)=4,MONTH(E$7)=5,MONTH(E$7)=6),MOD(YEAR($C9),4)=3,OR(MONTH($C9)=4,MONTH($C9)=5,MONTH($C9)=6)),AND(MOD(YEAR(E$7),4)=3,E$8="Q3", OR(MONTH(E$7)=7,MONTH(E$7)=8,MONTH(E$7)=9),MOD(YEAR($C9),4)=3,OR(MONTH($C9)=7,MONTH($C9)=8,MONTH($C9)=9)),AND(MOD(YEAR(E$7),4)=3,E$8="Q4", OR(MONTH(E$7)=10,MONTH(E$7)=11,MONTH(E$7)=12),MOD(YEAR($C9),4)=3,OR(MONTH($C9)=10,MONTH($C9)=11,MONTH($C9)=12)))	text	NO
E9:BS59	Expression	=OR(AND(OR(MONTH($C9)=1,MONTH($C9)=2,MONTH($C9)=3),OR(E$8="Q1",E$8="Q3")), AND(OR(MONTH($C9)=4,MONTH($C9)=5,MONTH($C9)=6),OR(E$8="Q2",E$8="Q4")), AND(OR(MONTH($C9)=7,MONTH($C9)=8,MONTH($C9)=9),OR(E$8="Q1",E$8="Q3")), AND(OR(MONTH($C9)=10,MONTH($C9)=11,MONTH($C9)=12),OR(E$8="Q2",E$8="Q4")))	text	NO
E9:BS59	Expression	=AND($C$6="Monthly",MATCH(E$7,'Actual Audit Dates'!$C2:$CT2-DAY('Actual Audit Dates'!$C2:$CT2)+1,0))	text	NO
E9:BS59	Expression	=AND($C$6="Monthly",OR(AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=1,MOD(YEAR($C9),4)=3,MONTH($C9)=1),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=2,MOD(YEAR($C9),4)=3,MONTH($C9)=2),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=3,MOD(YEAR($C9),4)=3,MONTH($C9)=3),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=4,MOD(YEAR($C9),4)=3,MONTH($C9)=4),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=5,MOD(YEAR($C9),4)=3,MONTH($C9)=5),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=6,MOD(YEAR($C9),4)=3,MONTH($C9)=6),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=7,MOD(YEAR($C9),4)=3,MONTH($C9)=7),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=8,MOD(YEAR($C9),4)=3,MONTH($C9)=8),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=9,MOD(YEAR($C9),4)=3,MONTH($C9)=9),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=10,MOD(YEAR($C9),4)=3,MONTH($C9)=10),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=11,MOD(YEAR($C9),4)=3,MONTH($C9)=11),AND(MOD(YEAR(E$7),4)=3,MONTH(E$7)=12,MOD(YEAR($C9),4)=3,MONTH($C9)=12)))	text	NO
E9:BS59	Expression	=AND($C$6="Monthly",OR(AND(MOD(MONTH(E$7),6)=1,MONTH($C9)=1),AND(MOD(MONTH(E$7),6)=2,MONTH($C9)=2),AND(MOD(MONTH(E$7),6)=3,MONTH($C9)=3),AND(MOD(MONTH(E$7),6)=4,MONTH($C9)=4),AND(MOD(MONTH(E$7),6)=5,MONTH($C9)=5),AND(MOD(MONTH(E$7),6)=6,MONTH($C9)=6),AND(MOD(MONTH(E$7),6)=7,MONTH($C9)=7),AND(MOD(MONTH(E$7),6)=8,MONTH($C9)=8),AND(MOD(MONTH(E$7),6)=9,MONTH($C9)=9),AND(MOD(MONTH(E$7),6)=10,MONTH($C9)=10),AND(MOD(MONTH(E$7),6)=11,MONTH($C9)=11),AND(MOD(MONTH(E$7),6)=12,MONTH($C9)=12)))	text	NO
E9:IW31,E36:IW59,E32:BS35	Expression	=AND(TODAY()>=E$7,TODAY()<F$7)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C6	List	Monthly,Quarterly
C3	List	Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
C4	List	2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035

Audit Tracking Visual.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Division	Plant #	Initial Cert Date	S1 Date	S2 Date	S3 Date	S4 Date	S5 Date	S6 Date	S7 Date	Cert Date	S1 Date	S2 Date	S3 Date	S4 Date	S5 Date	S6 Date	S7 Date	Cert Date	S1 Date	S2 Date
2	Residential	4	02/15/23	06/01/23	02/01/24	07/01/24	04/01/25	08/01/25	04/01/26	08/01/26	02/01/27
3	Residential	6	11/25/23	07/01/23	11/01/24	03/01/25	07/01/25	09/01/25		10/01/26	01/01/27
4	Residential	10	03/12/23	09/01/23	04/01/24	07/01/24		07/01/25	05/01/26	09/01/26	01/01/27
5	Residential	67
6	Residential	80
7	Residential	81
8	Residential	D7
9	Residential	WD
10
11	Contract	12
12	Contract	13
13	Contract	15
14	Contract	T1
15	Contract	WE
16	Contract	WF
17
18	Fibers	22
19	Fibers	65
Actual Audit Dates

 

[Micron]

Isn't this the same issue that you marked solved here?

Conditional Formatting for Comparing Dates

Hello Excel Experts, I am needing much help creating a conditional formatting custom formula to highlight the intersecting cells. See the attached example screenshot. Take a look at the 4 dates for Facility 15 (Row 81) and I have manually highlighted each corresponding dates that match the...

www.mrexcel.com

And then you marked this one (for every four years) as solved. Would think the same approach could be used for every four months?

Conditional Formatting Every 4 years From Start Date

Hello Excel Experts, See the uploaded images of my spreadsheet attached this thread. I am wanting to highlight the cell that occurs every 4 years from the Cert Date (Col B) or a start date. The Display (cell B4) will change the dates in the grey header cells from Monthly to Quarterly. The...

www.mrexcel.com

 

[94mustang]

Hey Micron,

It is the same worksheet but with Quarters. I did figure this one out and here is at least one solution:

AND($C$10="Quarterly",MATCH(CHOOSE(MONTH(E$11),1,1,1,2,2,2,3,3,3,4,4,4)&YEAR(E$11),CHOOSE(MONTH('Actual Audit Dates'!$C2:$CT2),1,1,1,2,2,2,3,3,3,4,4,4)&YEAR('Actual Audit Dates'!$C2:$CT2),0))

Thanks for remembering. I did use what you sent me before as a starting point. I literally figured it out today. Thank you so much for help on the previous post regarding the Monthy dates.

 

[94mustang]

Hello Micron,

Take a look at the image I uploaded. Why would the conditional formatting be sporadic with populating the appropriate cells? I have all the logic complete. I am interested in the 25% grey highlighted cells. It is because I am running out of memory or something else. Any ideas?

 

[94mustang]

Isn't this the same issue that you marked solved here?

Conditional Formatting for Comparing Dates

Hello Excel Experts, I am needing much help creating a conditional formatting custom formula to highlight the intersecting cells. See the attached example screenshot. Take a look at the 4 dates for Facility 15 (Row 81) and I have manually highlighted each corresponding dates that match the...

www.mrexcel.com

And then you marked this one (for every four years) as solved. Would think the same approach could be used for every four months?

Conditional Formatting Every 4 years From Start Date

Hello Excel Experts, See the uploaded images of my spreadsheet attached this thread. I am wanting to highlight the cell that occurs every 4 years from the Cert Date (Col B) or a start date. The Display (cell B4) will change the dates in the grey header cells from Monthly to Quarterly. The...

www.mrexcel.com

Hey Micron,

Not sure if you will receive my new post in this thread so I am replying here. The conditional formatting is working sporadically. What could be causing this issue? Thanks.

 

[Micron]

If you're using a complex formula then I cannot be of any assistance. Complex Excel formulae are not my thing.

 

[94mustang]

If you're using a complex formula then I cannot be of any assistance. Complex Excel formulae are not my thing.

I was able to reduce the conditional formatting formula and the cells are beginning to highlight now. Here are the formulas that I reduced them to:

Highlights every 4 years = AND($D$6="Monthly",OR( MONTH(F$10)&YEAR(F$10)=MONTH($D12)&YEAR($D12), MONTH($D12)&YEAR($D12)+4=MONTH(F$10)&YEAR(F$10)))

Highlights every 6 months = AND($D$6="Monthly",OR(MONTH(F$10)=MONTH($D12),MONTH(F$10)=MONTH($D12)+6))

 

[Micron]

That's all Greek to me. At my age, I have no desire to get proficient with formulae. I'll be happy to stick with vba.
Nice that you posted your solution - it might help someone else in the future.