Conditional format current month and the next 2 months

[mickyd67]

Hi All,

Any kind soul help me with this one?

I know you can conditionally format dates in the future etc, but it only goes out to the following month.

If I wanted to highlight June, July & August, as of today, is there a formula / condition I can use to do that?

 

[Joe4]

I assume that your values are valid dates, right?
Do you want it to just go out 2 months from today (August 10), or do you want to go all the way out to the end of August?

 

[awoohaw]

if you want to included all dates for the next 3 months, you could use this conditional formatting rule:
Assume the date in question is in cell A2.
The rule for A2 would be:

=if(and(A2>=Today(),A2<=edate(Today(),3))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			2024-05-25	2024-06-04	2024-06-09	2024-06-10	2024-06-20	2024-06-30	2024-07-10	2024-07-20	2024-07-30	2024-09-09	2024-09-10	2024-09-11	2024-09-21
3			FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
Sheet16

Cell Formulas
Range	Formula
D2,O2,G2:K2	D2	=C2+10
C3:O3	C3	=AND(C2>=TODAY(),C2<=EDATE(TODAY(),3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:O2	Expression	=AND(C2>=TODAY(),C2<=EDATE(TODAY(),3))	text	NO

 

[mickyd67]

I assume that your values are valid dates, right?
Do you want it to just go out 2 months from today (August 10), or do you want to go all the way out to the end of August?

Hi there - yeah they are. Basically 1st June, 1st July etc etc.

I am looking to highlight current month +2, so that would highlight June, July & August. To answer your question: Yes, would go out to the end of the 2nd month regardless of the date in the current month.

 

[mickyd67]

if you want to included all dates for the next 3 months, you could use this conditional formatting rule:
Assume the date in question is in cell A2.
The rule for A2 would be:

=if(and(A2>=Today(),A2<=edate(Today(),3))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			2024-05-25	2024-06-04	2024-06-09	2024-06-10	2024-06-20	2024-06-30	2024-07-10	2024-07-20	2024-07-30	2024-09-09	2024-09-10	2024-09-11	2024-09-21
3			FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
Sheet16

Cell Formulas
Range	Formula
D2,O2,G2:K2	D2	=C2+10
C3:O3	C3	=AND(C2>=TODAY(),C2<=EDATE(TODAY(),3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:O2	Expression	=AND(C2>=TODAY(),C2<=EDATE(TODAY(),3))	text	NO

Thanks - I'll give that a try.

 

[Joe4]

Hi there - yeah they are. Basically 1st June, 1st July etc etc.

I am looking to highlight current month +2, so that would highlight June, July & August. To answer your question: Yes, would go out to the end of the 2nd month regardless of the date in the current month.

If you want that, try this formula:

=AND(A2>TODAY(),A2<=EOMONTH(EDATE(A2,2),0))

 

[awoohaw]

      =AND(A2>=TODAY(),A2<=EOMONTH(TODAY(),3))

 

[Joe4]

Whoops, my formula should be:

=AND(A2>TODAY(),A2<=EOMONTH(EDATE(TODAY(),2),0))

or borrowing from awoohaw's logic:

=AND(A2>TODAY(),A2<=EOMONTH(TODAY(),2))

      =AND(A2>=TODAY(),A2<=EOMONTH(TODAY(),3))

awoohaw,
Note that formula goes to the end of September, not August!
That "3" needs to be a "2", like in my formula.

 

[awoohaw]

Whoops, my formula should be:

=AND(A2>TODAY(),A2<=EOMONTH(EDATE(TODAY(),2),0))

or borrowing from awoohaw's logic:

=AND(A2>TODAY(),A2<=EOMONTH(TODAY(),2))

awoohaw,
Note that formula goes to the end of September, not August!
That "3" needs to be a "2", like in my formula.

Meh. July doesn't exist! Even though I was born in it.

 

[mickyd67]

Edit - OK, I'll try the above