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

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
- 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
  - SergeiBaklan
    Diamond Contributor
    Nov 02, 2017
    Exactly, thank you. Entire rule could be
    =A1=MEDIAN(EDATE(TODAY(),-12),A1,EDATE(TODAY(),12))