Conditional format based on date

[bhavikpatel]

Hi all,

I have the following table:

	A	B	C	D	E	F	G	H
1	Month #	Month	Renewal	Item	2017	2018	2019	2020
2	1	Jan	01-Jan	Item 1	100	150	200	250
3	3	Mar	01-Mar	Item 2	200	250	300	350
4	5	May	01-May	Item 3	300	350	400	450
5	7	Jul	01-Jul	Item 4	400	450	500	550
6	8	Aug	11-Aug	Item 5	500	550	600	650
7	8	Aug	11-Aug	Item 6	600	650	700	750
8	8	Aug	11-Aug	Item 7	700	750	800	850
9	8	Aug	11-Aug	Item 8	800	850	900	950
10	8	Aug	11-Aug	Item 9	900	950	1000	1050
11	9	Sep	03-Sep	Item 10	1000	1050	1100	1150
12	9	Sep	26-Sep	Item 11	1100	1150	1200	1250
13	10	Oct	01-Oct	Item 12	1200	1250	1300	1350
14	10	Oct	01-Oct	Item 13	1300	1350	1400	1450
15	11	Nov	01-Nov	Item 14	1400	1450	1500	1550
16	11	Nov	01-Nov	Item 15	1500	1550	1600	1650
17	11	Nov	30-Nov	Item 16	1600	1650	1700	1750
18	12	Dec	01-Dec	Item 17	1700	1750	1800	1850

<colgroup><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

I want to conditional format so the amounts in columns E onwards are highlighted if they are up for renewal within the next 12 months from today.

I basically want it to be so as we stand today (9th November 2017) - the current highlighted cells would be E17 and E18 as well as F2-F16. The rest would remain as they are. Then by 2nd December F2-F18 would be highlighted, before 1 Jan when it would change again for Item1.

Is there a relatively painless conditional formatting formula I could input to achieve this? (the year columns go on for a while so would like to avoid formatting each cell)

Thanks in advance for the help!

 

[Scott T]

Select the cells you want to conditional format (E2:H18 in your sample)

in conditional formatting use this formula. Select your formatting and click OK

=AND(DATE(E$1,MONTH($C2),DAY($C2))<DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())),TODAY()<DATE(E$1,MONTH($C2),DAY($C2)))

 

[bhavikpatel]

<date(year(today())+1,month(today()),day(today())),today()<date(e$1,month($c2),day($c2)))[ code][="" quote]

Select the cells you want to conditional format (E2:H18 in your sample)

in conditional formatting use this formula. Select your formatting and click OK

=AND(DATE(E$1,MONTH($C2),DAY($C2))<date(year(today())+1,month(today()),day(today())),today()<date(e$1,month($c2),day($c2)))[ code][="" quote]
 ]</date(year(today())+1,month(today()),day(today())),today()<date(e$1,month($c2),day($c2)))[>

Hi Scott, 

Thanks for the reply!

Unfortunately, the above formula makes all calls E2:H18 highlighted. I only wanted the cells in the next 12 months to be highlighted. Any ideas?

Thanks</date(year(today())+1,month(today()),day(today())),today()<date(e$1,month($c2),day($c2)))[>

 

[Scott T]

Forum cut off the rest of the formula try

=AND(DATE(E$1,MONTH($C2),DAY($C2))>TODAY(),DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY()))>=DATE(E$1,MONTH($C2),DAY($C2)))

 

[bhavikpatel]

That works perfectly - thank you so much.

 

[Scott T]

Glad to help.