Conditional Formatting of Date based on Today's Date

[Rajendra_kodavaty]

Hey Team,

Thanks for your support along the way.

I Have a column CLR timeline that has dates as below, which are actually a result of the formula of a different column
[TABLE="width: 130"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CLR Time line[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]17-Dec-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]16-Sep-17[/TD]
[/TR]
[TR]
[TD]17-Jan-18[/TD]
[/TR]
[TR]
[TD]14-Feb-18[/TD]
[/TR]
[TR]
[TD]17-Oct-17[/TD]
[/TR]
[TR]
[TD]17-Oct-17[/TD]
[/TR]
[TR]
[TD]17-Oct-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]17-Oct-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]17-Oct-17[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]14-Feb-18[/TD]
[/TR]
[TR]
[TD]16-Nov-17[/TD]
[/TR]
[TR]
[TD]17-Jan-18[/TD]
[/TR]
[TR]
[TD]24-Nov-17[/TD]
[/TR]
[TR]
[TD]25-Jan-18[/TD]
[/TR]
[TR]
[TD]22-Feb-18[/TD]
[/TR]
</tbody>[/TABLE]

I wanted to apply conditional formatting to this particular column, where if the

• Difference between that date and today's date is >= 30 then Cell will be green
• Number of days between the timeline and today's date is >= 20& <30 then cells should turn Yellow
• The difference in number of days is <20 the Cells should turn Red.

I am not able to find this option to apply today's date in Conditional formatting. Any suggestions are appreciated

Thanks

 

[ttray33y]

hey team,

thanks for your support along the way.

I have a column clr timeline that has dates as below, which are actually a result of the formula of a different column
[table="width: 130"]
<tbody>[tr]
[td]clr time line[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]17-dec-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]16-sep-17[/td]
[/tr]
[tr]
[td]17-jan-18[/td]
[/tr]
[tr]
[td]14-feb-18[/td]
[/tr]
[tr]
[td]17-oct-17[/td]
[/tr]
[tr]
[td]17-oct-17[/td]
[/tr]
[tr]
[td]17-oct-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]17-oct-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]17-oct-17[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]14-feb-18[/td]
[/tr]
[tr]
[td]16-nov-17[/td]
[/tr]
[tr]
[td]17-jan-18[/td]
[/tr]
[tr]
[td]24-nov-17[/td]
[/tr]
[tr]
[td]25-jan-18[/td]
[/tr]
[tr]
[td]22-feb-18[/td]
[/tr]
</tbody>[/table]

i wanted to apply conditional formatting to this particular column, where if the

• difference between that date and today's date is >= 30 then cell will be green
• number of days between the timeline and today's date is >= 20& <30 then cells should turn yellow
• the difference in number of days is <20 the cells should turn red.

i am not able to find this option to apply today's date in conditional formatting. Any suggestions are appreciated

thanks

green
=if(($a2-today())>=30,true,false)

yellow
=if(($a2-1)-(today()-1)>=20,true,false)

red
=if(($a2-today())<20,true,false)

 

[Rajendra_kodavaty]

green
=if(($a2-today())>=30,true,false)

yellow
=if(($a2-1)-(today()-1)>=20,true,false)

red
=if(($a2-today())<20,true,false)

Thanks for your response. But where do i enter the above code

 

[Rajendra_kodavaty]

Thanks for your response. But where do i enter the above code

Figured it out

Thanks!