Forum Discussion

Copper Contributor

Oct 31, 2017

Formatting a cell for date range

Hello all I need to format a cell so that when a date is entered that is within 12 months of today's date the cell is highlighted in green and when the date exceeds 12 months from today's date th...

Excel Desktop

Formulas & Functions

SergeiBaklan

Diamond Contributor

Oct 31, 2017

Hi Gethyn,

You may apply conditional formatting rule for green color like

=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<=A1

and similar for the red one

=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY()))>=A1

applying it to your range with dates / cell (if it's only one cell)

gethyn.edwards
Copper Contributor
Oct 31, 2017
Thank you for the response Sergei

Is it possible to combine these formulas so that the cell automatically changes from green to red after 12 months?
- SergeiBaklan
  Diamond Contributor
  Oct 31, 2017
  Gethyn,
  
  Sorry, i misunderstood you first post. In general you have one rule for each color, within in each expression which returns TRUE or FALSE works.
  for green
  =(A1>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(A1<=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))
  and for the red converted above rule
  =NOT((A1>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(A1<=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY()))))
  If plus/minus one day is not important you may simplify using something like TODAY()-365 in expression instead of DATE(..)
  - gethyn.edwards
    Copper Contributor
    Oct 31, 2017
    That was my understanding, but was hoping there was some clever way to combine rules that I was unaware of.
    
    Thanks again Sergei