Conditional Highlighting Cells by Fiscal Quarters?

[olimits7]

Hi,

Please see below, currently I'm manually highlighting the colors based on different quarters starting from the "1st Inv. Date" as the "1st Month", I'm trying to see if there is a way this can be automatically updated using "Conditional Formatting".

So, as an example, taking the 09/01/23 invoice date; Q3 would fall under columns E, F, G and is highlighted in orange.

I was trying to set this up as 2 options:

• Using a pull-down menu and depending on what quarter I select it will automatically highlight the appropriate cells.
  
  
• Using conditional formatting, just set up different rules where it would highlight the correct cells in different colors representing the different quarters like I manually did below.

Thank you!

 

[dreid1011]

Here is for your second option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3		9/1/2023
4
5
6				1	2	3	4	5	6	7	8	9	10	11	12
7	Customer Name	1st Inv. Date		1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11th Month	12th Month
8		4/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
9		5/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
10		6/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
11		7/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
12		8/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
13		9/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
14		10/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
15		11/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:O15	Expression	=AND(MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16)	text	NO
D8:O15	Expression	=AND(MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13)	text	NO
D8:O15	Expression	=AND(MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10)	text	NO
D8:O15	Expression	=MONTH($B8)+D$6<=7	text	NO

 

[awoohaw]

I did it with quarter numbers in your cells but I hope you get the idea, and continues into the next year.

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Q1 Yellow
2	Q2 Green
3	Q3 Orange
4	Q4 Blue
5
6			1	2	3	4	5	6	7	8	9	10	11	12
7	Customer	1st Inv Date	1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11 Month	12 Month
8	Customer1	2023-04-01	1	1	1	2	2	2	3	3	3	4	4	4
9	Customer2	2023-05-01	1	1	2	2	2	3	3	3	4	4	4	1
10	Customer3	2023-06-01	1	2	2	2	3	3	3	4	4	4	1	1
11	Customer4	2023-07-01	2	2	2	3	3	3	4	4	4	1	1	1
12	Customer5	2023-08-01	2	2	3	3	3	4	4	4	1	1	1	2
13	Customer6	2023-09-01	2	3	3	3	4	4	4	1	1	1	2	2
14	Customer7	2023-10-01	3	3	3	4	4	4	1	1	1	2	2	2
15	Customer8	2023-11-01	3	3	4	4	4	1	1	1	2	2	2	3
16
17
18	Formula in	cell C8	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)
olimits7

Cell Formulas
Range	Formula
C8:N15	C8	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:N15	Expression	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)=4	text	NO
C8:N15	Expression	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)=3	text	NO
C8:N15	Expression	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)=2	text	NO
C8:N15	Expression	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)=1	text	NO

 

[dreid1011]

Updated rules to handle both options, data validation list in B3 contains "ALL, Q1, Q2, Q3, Q4":

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2					LEGEND
3		Q3			Q1		Q2
4					Q3		Q4
5
6				1	2	3	4	5	6	7	8	9	10	11	12
7	Customer Name	1st Inv. Date		1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11th Month	12th Month
8		4/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
9		5/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
10		6/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
11		7/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
12		8/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
13		9/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
14		10/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
15		11/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q4"),MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q3"),MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q2"),MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q1"),MONTH($B8)+D$6<=7)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	List	ALL,Q1,Q2,Q3,Q4

 

[olimits7]

Updated rules to handle both options, data validation list in B3 contains "ALL, Q1, Q2, Q3, Q4":

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2					LEGEND
3		Q3			Q1		Q2
4					Q3		Q4
5
6				1	2	3	4	5	6	7	8	9	10	11	12
7	Customer Name	1st Inv. Date		1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11th Month	12th Month
8		4/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
9		5/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
10		6/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
11		7/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
12		8/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
13		9/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
14		10/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
15		11/1/2023		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q4"),MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q3"),MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q2"),MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10)	text	NO
D8:O15	Expression	=AND(OR($B$3="ALL",$B$3="Q1"),MONTH($B8)+D$6<=7)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	List	ALL,Q1,Q2,Q3,Q4

This seems to work well, thank you for the reply!

The only issue I'm running into is I have blank rows so for some cells under the B column it's blank but the conditional formatting is highlighted the whole rows; is there a way to exclude the conditional formatting from being applied if there is a cell in column B that's blank?

Thank you!

 

[awoohaw]

A quick way to fix that would be to wrap each conditional format rule with an
=IF(cellref = "", FALSE, the rest of conditional rule)

 

[olimits7]

A quick way to fix that would be to wrap each conditional format rule with an
=IF(cellref = "", FALSE, the rest of conditional rule)

Yes, that seemed to work for the blank invoice date cells. However, now I'm seeing these other issues that I can't seem to resolve.

• For some reason, if the invoice date is 11/01/22 it doesn't highlight the row at all for the correct Q1 month (April, May, June) sections.
  
  
• And on the invoice date month that shows 01/01/23, you can see in the "CURRENTLY" section below it's highlighting the full row from the beginning and not just the Q1 month (Apr, May, June) sections only.
  
  
• Also, is there a way I can exclude highlighting on cells where it shows zero? So below in the "CURRENTLY" section if I set it to Q2 it highlights those sections but I would like to exclude the highlighting if it shows a zero amount.

Thank you!

 

[awoohaw]

Ok, well, Id id not write the CF rules that you probably have in that image. This is how mine have been updated:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Q1 Yellow
2	Q2 Green
3	Q3 Orange
4	Q4 Blue
5
6			1	2	3	4	5	6	7	8	9	10	11	12
7	Customer	1st Inv Date	1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11 Month	12 Month
8	Customer1	2023-04-01	1	1	1	2	2	2	3	3	3	4	4	4
9	Customer2	2023-05-01	1	1	2	2	2	3	3	3	4	4	4	1
10
11	Customer3	2023-06-01	1	2	2	2	3	3	3	4	4	4	1	1
12	Customer4	2023-07-01	2	2	2	3	3	3	4	4	4	1	1	1
13
14	Customer5	2023-08-01	2	2	3	3	3	4	4	4	1	1	1	2
15	Customer6	2023-09-01	2	3	3	3	4	4	4	1	1	1	2	2
16	Customer7	2023-10-01	3	3	3	4	4	4	1	1	1	2	2	2
17	Customer8	2023-11-01	3	3	4	4	4	1	1	1	2	2	2	3
18
19
olimits7

Cell Formulas
Range	Formula
C8:N9,C14:N17,C11:N12	C8	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=4	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=3	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=2	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=1	text	NO

This is my guess as to how to update the "selection" version's CF rules:
=IF($B8 = "", FALSE, AND(OR($B$3="ALL",$B$3="Q4"),MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16) )
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q3"),MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13))
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q2"),MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10))
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q1"),MONTH($B8)+D$6<=7))

 

[olimits7]

Ok, well, Id id not write the CF rules that you probably have in that image. This is how mine have been updated:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Q1 Yellow
2	Q2 Green
3	Q3 Orange
4	Q4 Blue
5
6			1	2	3	4	5	6	7	8	9	10	11	12
7	Customer	1st Inv Date	1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11 Month	12 Month
8	Customer1	2023-04-01	1	1	1	2	2	2	3	3	3	4	4	4
9	Customer2	2023-05-01	1	1	2	2	2	3	3	3	4	4	4	1
10
11	Customer3	2023-06-01	1	2	2	2	3	3	3	4	4	4	1	1
12	Customer4	2023-07-01	2	2	2	3	3	3	4	4	4	1	1	1
13
14	Customer5	2023-08-01	2	2	3	3	3	4	4	4	1	1	1	2
15	Customer6	2023-09-01	2	3	3	3	4	4	4	1	1	1	2	2
16	Customer7	2023-10-01	3	3	3	4	4	4	1	1	1	2	2	2
17	Customer8	2023-11-01	3	3	4	4	4	1	1	1	2	2	2	3
18
19
olimits7

Cell Formulas
Range	Formula
C8:N9,C14:N17,C11:N12	C8	=INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=4	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=3	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=2	text	NO
C8:N17	Expression	=IF($B8="",FALSE,INT(((MONTH($B8)+C$6-2)-(IF(MONTH($B8)+C$6-2>14,12,0)))/3))=1	text	NO

This is my guess as to how to update the "selection" version's CF rules:
=IF($B8 = "", FALSE, AND(OR($B$3="ALL",$B$3="Q4"),MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16) )
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q3"),MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13))
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q2"),MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10))
=IF($B8 = "", FALSE,AND(OR($B$3="ALL",$B$3="Q1"),MONTH($B8)+D$6<=7))

Thank you for the quick reply!

I ended up trying your formulas and they work well the only issue I'm seeing is the following. I want to show just the current fiscal year highlighted, so for example only dates that would get highlighted between April'23 to March'24.

So, I see if the date is 05/01/23 it highlights May/June months properly in the 1st/2nd Month columns but then it's also highlighting the 12th month too which would calculate to April'24 and shouldn't get highlighted.

Similar issue if the date is 07/01/23; it's highlighting the April/May/June months in the 10th/11th/12th column which is correct, if just going off the month, but these columns would fall under the next fiscal year being 04/01/24 to 06/01/24 and shouldn't get highlighted.

Thank you.

 

[awoohaw]

Okay, this has 2023 hard coded in the CF rules. You can probably automate that a bit better.

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7			1	2	3	4	5	6	7	8	9	10	11	12
8	Customer	1st Inv Date	1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11 Month	12 Month
9	Customer1	2023-Apr-01	1	1	1	2	2	2	3	3	3	4	4	4
10	Customer2	2023-May-01	1	1	2	2	2	3	3	3	4	4	4	FALSE
11			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
12	Customer3	2023-Jun-01	1	2	2	2	3	3	3	4	4	4	FALSE	FALSE
13	Customer4	2023-Jul-01	2	2	2	3	3	3	4	4	4	FALSE	FALSE	FALSE
14			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15	Customer5	2023-Aug-01	2	2	3	3	3	4	4	4	FALSE	FALSE	FALSE	FALSE
16	Customer6	2023-Sep-01	2	3	3	3	4	4	4	FALSE	FALSE	FALSE	FALSE	FALSE
17	Customer7	2023-Oct-01	3	3	3	4	4	4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
18	Customer8	2023-Nov-01	3	3	4	4	4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19
20
21	Formula in	cell C9	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3)),FALSE)
22
23			1	2	3	4	5	6	7	8	9	10	11	12
24	Customer	1st Inv Date	1st Month	2nd Month	3rd Month	4th Month	5th Month	6th Month	7th Month	8th Month	9th Month	10th Month	11 Month	12 Month
25	Customer1	2023-Apr-01	100	100	100	100	100	100	100	100	100	100	100	100
26	Customer2	2023-May-01	100	100	100	100	100	100	100	100	100	100	100	100
27		2021-Jan-01	100	100	100	100	100	100	100	100	100	100	100	100
28	Customer3	2023-Jun-01	100	100	100	100	100	100	100	100	100	100	100	100
29	Customer4	2023-Jul-01	100	100	100	100	100	100	100	100	100	100	100	100
30			100	100	100	100	100	100	100	100	100	100	100	100
31	Customer5	2023-Aug-01	100	100	100	100	100	100	100	100	100	100	100	100
32	Customer6	2023-Sep-01	100	100	100	100	100	100	100	100	100	100	100	100
33	Customer7	2023-Oct-01	100	100	100	100	100	100	100	100	100	100	100	100
34	Customer8	2023-Nov-01	100	100	100	100	100	100	100	100	100	100	100	100
35
olimits7

Cell Formulas
Range	Formula
C9:N18	C9	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3)),FALSE)
C21	C21	=FORMULATEXT(C9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C9:N18,C23:N34	Expression	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3))=1,FALSE)	text	NO
C9:N18,C23:N34	Expression	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3))=2,FALSE)	text	NO
C9:N18,C23:N34	Expression	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3))=3,FALSE)	text	NO
C9:N18,C23:N34	Expression	=IFERROR(IF(YEAR(EDATE($B9,-4+C$7))<>2023,FALSE, INT(((MONTH($B9)+C$7-2)-(IF(MONTH($B9)+C$7-2>14,12,0)))/3))=4,FALSE)	text	NO