Forum Discussion

gethyn.edwards's avatar
gethyn.edwards
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 the cell is highlighted in red.

 

Thanks for your help

 

Gethyn

Excel Desktop
Formulas & Functions

10 Replies

  • Michael_Avidan's avatar
    Michael_Avidan
    MVP
    Nov 04, 2017

    gethyn.edwards,

    Try my following suggested formulas:

    For GREEN:

    =(A1>=EDATE(TODAY(),-12))*(A1<=EDATE(TODAY(),12))

     

    For RED:

    =1-(A1>=EDATE(TODAY(),-12))*(A1<=EDATE(TODAY(),12))

    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” Excel MVP – Excel     (2009-2018)
    ISRAEL

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 04, 2017

      Michael, is it any difference in results compare to MEDIAN?

      • Michael_Avidan's avatar
        Michael_Avidan
        MVP
        Nov 04, 2017
        SergeiBaklan wrote:

        Michael, is it any difference in results compare to MEDIAN?

        Nope (maybe a "bit" shorter).
        Sorry, I didn't noticed your MEDIAN suggestion nor did I get an E-Mail notifying that someone added a post in a discussion I participated.
        Do you know how to switch-on the option to receive notification E-Mails ?

        P.S.
        Will you accept the following to be a bit more "ink saver" ?
        =A1=MEDIAN(EDATE(TODAY(),{-12,12}),A1)

        TXS,
        Michael

  • SergeiBaklan's avatar
    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's avatar
      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's avatar
        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(..)

         

         

Resources