Conditional format based on date

[bhavikpatel]

Hi all,

I have the following table:

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


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.