Conditional Highlighting Cells by Fiscal Quarters?

olimits7 · Nov 3, 2023

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 · Nov 5, 2023

olimits7 said:
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.

Okay, so at least for my rules, it looks like I need to adjust them to handle the year as well, but I have updated them currently to handle no date in Column B, zero amounts in the cells, and Jan, Feb, Mar dates.

(XL2BB is not working on my 2013 version at home)

Q1:

Excel Formula:

=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q1"),D8<>0,MONTH($B8)+D$6>4,MONTH($B8)+D$6<=7))

Q2:

Excel Formula:

=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q2"),D8<>0,MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10))

Q3:

Excel Formula:

=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q3"),D8<>0,MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13))

Q4:

Excel Formula:

=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q4"),D8<>0,MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16))

Thanks to awoohaw for the =IF(B8=""....) bit.

dreid1011 · Nov 5, 2023

Ok, the final result is not pretty... I sure hope there is a nicer option, but see if this does what you want:

Edit: I'll keep playing with this because it is only effective through 12/1/23. No 2024 dates seem to work.

Book1 11-5-23.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

LEGEND

3

ALL

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

7/1/2022

100

9

8/1/2022

100

10

9/1/2022

100

11

10/1/2022

100

12

11/1/2022

100

0

100

13

12/1/2022

100

14

1/1/2023

100

15

100

0

100

16

3/1/2023

100

17

4/1/2023

100

18

6/1/2023

100

0

100

19

7/1/2023

100

20

8/1/2023

100

0

100

21

9/1/2023

100

22

10/1/2023

100

23

11/1/2023

100

24

12/1/2023

100

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:O24	Expression	=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q4"),D8<>0,OR(ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=11.38,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=12.2,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)*100),2)=13.01)))	text	NO
D8:O24	Expression	=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q3"),D8<>0,OR(ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=8.94,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=9.76,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=10.57,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=18.85,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)*100),2)=19.67)))	text	NO
D8:O24	Expression	=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q2"),D8<>0,OR(ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=6.5,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=7.32,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=8.13,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=16.39,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=17.21,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=18.03)))	text	NO
D8:O24	Expression	=IF($B8="",FALSE,AND(OR($B$3="ALL",$B$3="Q1"),D8<>0,OR(ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=4.07,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=4.88,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=5.69,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=13.93,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=14.75,ROUND((INT(MONTH($B8)+D$6)/ROUNDDOWN($B8/365.25,0)100),2)=15.57)))	text	NO

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

olimits7 · Nov 5, 2023

awoohaw said:
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

Yes, this seems to work properly...Ty.

Currently, it highlights all quarters but is there a way to set up this conditional formatting to incorporate it so it works with a pull-down menu so I can have it show the way it does now but then also if I just want to see a specific quarter only I can change it to just show Q2, for example.

With the other formula that @dreid1011 provided I was able to get this to work with a combo pull-down menu by using the following below but I can't seem to figure out how to incorporate this into your formula.

Excel Formula:

=IF($B8="",,AND(OR($B$3=1,$B$3=2),MONTH($B8)+D$6>4,MONTH($B8)+D$6<=7))

=IF($B8="",,AND(OR($B$3=1,$B$3=3),MONTH($B8)+D$6>7,MONTH($B8)+D$6<=10))

=IF($B8="",,AND(OR($B$3=1,$B$3=4),MONTH($B8)+D$6>10,MONTH($B8)+D$6<=13))

=IF($B8="",,AND(OR($B$3=1,$B$3=5),MONTH($B8)+D$6>13,MONTH($B8)+D$6<=16))

Thank you.

olimits7 · Nov 5, 2023

Ok, I think I was able to update your formula ( @awoohaw ) to work with a combo pull-dow menu by using the following; it seems to be working properly so I think I did this properly.

Excel Formula:

=AND(OR($A$1="ALL",$A$1="Q1"),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))

=AND(OR($A$1="ALL",$A$1="Q2"),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))

=AND(OR($A$1="ALL",$A$1="Q3"),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))

=AND(OR($A$1="ALL",$A$1="Q4"),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))

dreid1011 · Nov 5, 2023

If you're satisfied with what you have, I will stop trying to make this work. But I'll show you what I have currently. The 4 red outlined sections are technically within bounds of the formula, but need to be ruled out and I don't know how to exclude them right now. This incorporates a date entered in a cell to identify the current year as seen in D3.

And just for reference, these are the rules currently.

olimits7 · Nov 6, 2023

dreid1011 said:
If you're satisfied with what you have, I will stop trying to make this work. But I'll show you what I have currently. The 4 red outlined sections are technically within bounds of the formula, but need to be ruled out and I don't know how to exclude them right now. This incorporates a date entered in a cell to identify the current year as seen in D3.

View attachment 101511

And just for reference, these are the rules currently.
View attachment 101512

What you posted before seems to work great for my needs...Ty.

@dreid1011 and @awoohaw thank you both for your replies, both methods work well and I'm happy this works properly now with the conditional formatting and pull-down menu; it makes it a lot easier to view on the eyes!

LOL.

dreid1011 · Nov 6, 2023

olimits7 said:
What you posted before seems to work great for my needs...Ty.

@dreid1011 and @awoohaw thank you both for your replies, both methods work well and I'm happy this works properly now with the conditional formatting and pull-down menu; it makes it a lot easier to view on the eyes! LOL.

Wonderful, and I am happy to help.

awoohaw · Nov 6, 2023

Happy to help. I'm happy you found a solution here.

Best Wishes!

Conditional Highlighting Cells by Fiscal Quarters?

olimits7

Board Regular

dreid1011

Well-known Member

Excel Facts

dreid1011

Well-known Member

olimits7

Board Regular

olimits7

Board Regular

dreid1011

Well-known Member

olimits7

Board Regular

dreid1011

Well-known Member

awoohaw

Well-known Member

Similar threads

Forum statistics

Share this page

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)

Conditional Highlighting Cells by Fiscal Quarters?

Board Regular

Well-known Member

Excel Facts

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock