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...

Formulas & Functions

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)

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
  - Michael_Avidan
    MVP
    Nov 02, 2017
    @ Gethyn,
    I assume the first suggested approach can be much shorter.
    Chek out: =EDATE(TODAY(),-12)<=A1
    --------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” Excel MVP – Excel (2009-2018)
    ISRAEL

Resources