Conditional Formatting on Quarterly Dates

94mustang

Board Regular
Joined
Dec 13, 2011
Messages
133
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB
1
2
3Start MonthJan
4Start Year2023
5Start Date:01/01/23
6DisplayQuarterly
71Jan-23Apr-23Jul-23Oct-23Jan-24Apr-24Jul-24Oct-24Jan-25Apr-25Jul-25Oct-25Jan-26Apr-26Jul-26Oct-26Jan-27Apr-27Jul-27Oct-27Jan-28Apr-28Jul-28Oct-28Jan-29Apr-29Jul-29Oct-29Jan-30Apr-30Jul-30Oct-30Jan-31Apr-31Jul-31Oct-31Jan-32Apr-32Jul-32Oct-32Jan-33Apr-33Jul-33Oct-33Jan-34Apr-34Jul-34Oct-34Jan-35Apr-35
8DivisionPlant #Cert DateQ1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2
9Residential402/15/23
10Residential611/25/23
11Residential1003/12/23
12Residential67 
13Residential80 
14Residential81 
Audit Tracking Visual
Cell Formulas
RangeFormula
C5C5=DATE(C4,D7,1)
D7D7=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))))))))))))
E7E7=timeline_start
F7:BB7F7=IF(display_category="Quarterly",EDATE(E7,3),IF(display_category="Monthly",EDATE(E7,1),WORKDAY.INTL(E7,1,"0000011")))
E8:BB8E8=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:C14C9=IF('Actual Audit Dates'!C2="","",IFERROR(VLOOKUP(B9,'Actual Audit Dates'!B1:C51,2,FALSE),""))
Named Ranges
NameRefers ToCells
display_category='Audit Tracking Visual'!$C$6E7:BB8
start_date='Audit Tracking Visual'!$C$5E7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9:BS59Expression=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")))textNO
E9:BS59Expression=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)))textNO
E9:BS59Expression=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")))textNO
E9:BS59Expression=AND($C$6="Monthly",MATCH(E$7,'Actual Audit Dates'!$C2:$CT2-DAY('Actual Audit Dates'!$C2:$CT2)+1,0))textNO
E9:BS59Expression=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)))textNO
E9:BS59Expression=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)))textNO
E9:IW31,E36:IW59,E32:BS35Expression=AND(TODAY()>=E$7,TODAY()<F$7)textNO
Cells with Data Validation
CellAllowCriteria
C6ListMonthly,Quarterly
C3ListJan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
C4List2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035



Audit Tracking Visual.xlsx
ABCDEFGHIJKLMNOPQRSTU
1DivisionPlant #Initial Cert DateS1 DateS2 DateS3 DateS4 DateS5 DateS6 DateS7 DateCert DateS1 DateS2 DateS3 DateS4 DateS5 DateS6 DateS7 DateCert DateS1 DateS2 Date
2Residential402/15/2306/01/2302/01/2407/01/2404/01/2508/01/2504/01/2608/01/2602/01/27
3Residential611/25/2307/01/2311/01/2403/01/2507/01/2509/01/2510/01/2601/01/27
4Residential1003/12/2309/01/2304/01/2407/01/2407/01/2505/01/2609/01/2601/01/27
5Residential67
6Residential80
7Residential81
8ResidentialD7
9ResidentialWD
10
11Contract12
12Contract13
13Contract15
14ContractT1
15ContractWE
16ContractWF
17
18Fibers22
19Fibers65
Actual Audit Dates
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Isn't this the same issue that you marked solved here?

And then you marked this one (for every four years) as solved. Would think the same approach could be used for every four months?
 
Upvote 0
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.
 
Upvote 0
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?
 

Attachments

  • Sporatic Conditional Formatting.png
    Sporatic Conditional Formatting.png
    69.4 KB · Views: 11
Upvote 0
Isn't this the same issue that you marked solved here?

And then you marked this one (for every four years) as solved. Would think the same approach could be used for every four months?
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.
 
Upvote 0
If you're using a complex formula then I cannot be of any assistance. Complex Excel formulae are not my thing.
 
Upvote 0
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))
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top