Conditional Format by month + months due

[misstoffeepenny]

Hi
I am trying to conditional format the dates inputted based on the month plus the number of months input into column E.

I want to create 2 formats one for past due in red and one for due in 3 months in yellow

Can anyone help please?

 

[Toadstool]

Hi Misstoffeepenny,

This should work for you but please note the CF for red should appear first (to avoid having to put a range on the yellow check).

Misstoffeepenny.xlsx
	E	F	G	H	I	J	K
3	Frequency	Head1	Head2	Head3	Head4	Head5	Head6
4
5	12	x	1/1/2020	1/1/2021	1/1/2022	1/1/2001	10/4/2020
6	12	x	9/9/2020	10/8/2020	11/11/2020
7	36	2/2/2020	1/1/2020	1/1/2021		1/1/2001	1/1/1998
8	48	1/1/2017	12/1/2017	10/8/2020	11/11/2020
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:K9999	Expression	=AND(F5<>"",EDATE(TODAY(),-$E5)>F5)	text	NO
F5:K9999	Expression	=AND(F5<>"",EDATE(TODAY(),3-$E5)>F5)	text	NO

 

[MaxKipman]

Hi. On similar note:

I'm trying to make a personal finances spreadsheet (using MS Excel for Mac, version 16.60, Office 365 subscription), which will alert me to bills' expiration dates.

I'm using this function, with conditional formatting:
=IFS(F4="";"";F4>=6;"";F4>=3;"attention";F4<=0;"overdue")
The F cell numbers refer to the number of days till the due date, using a calculation =E4-$B$1 (where B1 is =today())

This is working fine, to this point.

Once the bill is paid, however, I would like to add this information and I can't seem to make it work. Would I add a condition to this same function? Can I use a function in a different cell to format the DUE DATE cell (and paint it green, with PAID on it)?

Thanks again,

MK