Conditional Formatting - Dates

[charcoop14]

Hello,
I am trying to create some rules where the cells will change colors based on two dates and a running total Month by Year. See Below

Expiration Date	Commencement Date	Jan 2023	Feb 2023	March 2023	April 2023
01/31/2023	04/05/2023	Colored Cell - Expiration Date			Colored Cell - Commencement Date
03/31/2023	04/05/2023			Colored Cell - Expiration Date	Colored Cell - Commencement Date

As seen above, I have one date to match to a month for one column, and another date to match with a month for another column.

Thanks for any help.

 

[etaf]

whats the running total ?
do you want just 1 colour
or different colours for
Expiration Date
Commencement Date
seem wrong way round to me


how about

Book6
	A	B	C	D	E	F
1	Expiration Date	Commencement Date	Jan-23	Feb-23	Mar-23	Apr-23
2	1/31/23	4/5/23	Colored Cell - Expiration Date			Colored Cell - Commencement Date
3	3/31/23	4/5/23			Colored Cell - Expiration Date	Colored Cell - Commencement Date
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:F3	Expression	=AND($B2>=C$1, $B2<=EOMONTH(C$1,0))	text	NO
C2:F3	Expression	=AND($A2>=C$1, $A2<=EOMONTH(C$1,0))	text	NO

 

[charcoop14]

I want to create different colors and even add an Icon (Colored ell with bold "E") for expiration for example. I will try the above formulas, thanks.

 

[etaf]

how about using an IF
to get a Capital E for expiration and C for commencement

Book6
	A	B	C	D	E	F
1	Expiration Date	Commencement Date	Jan-23	Feb-23	Mar-23	Apr-23
2	1/31/23	4/5/23	E			C
3	3/31/23	4/5/23			E	C
Sheet1

Cell Formulas
Range	Formula
C2:F3	C2	=IF(AND($A2>=C$1, $A2<=EOMONTH(C$1,0)),"E",IF(AND($B2>=C$1, $B2<=EOMONTH(C$1,0)),"C",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:F3	Expression	=AND($B2>=C$1, $B2<=EOMONTH(C$1,0))	text	NO
C2:F3	Expression	=AND($A2>=C$1, $A2<=EOMONTH(C$1,0))	text	NO

 

[charcoop14]

Thanks!
What is there is a situation where both cells are same date, how do I make them a cell color of green fading into red?

 

[etaf]

i dont know how to do that - as it requires a colour scale - which normally has something like numbers in the cell
can make it a different colour with EC in maybe
i'm assuming thats when A2 is in the same month as B2