+ Reply to Thread
Results 1 to 5 of 5

Conditional Format based on date ranges

  1. #1
    Corey
    Guest

    Conditional Format based on date ranges

    I am trying to set the following to have conditional formatting:

    >Current date < Current date+14 days = Green shade
    >Current date+15<Currentdate+30=Yellow shade

    <=Current date=Red shade

    I am having trouble with the Between formulas.
    I have tried something like:
    Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade
    Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade
    Cell value is GREATER THAN: [NOW()+31] Red shade

    But i get no CF?

    The values ARE a calculated DATE value, Does this mean i should use the
    FORMULA IS instead of the Cell is?

    I then ONLY get Value is and Not the option of IS BETWEEN....


    Any ideas
    i am sure it is a simple formula sintax error i am doing wrong
    Regards

    Corey



  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    The order in which you apply the conditions is important. You can use "formula is" but "cell value is" works fine too. Use something like this

    condition 1
    "cell value is" Greater than =TODAY()+30
    red format

    condition 2
    "cell value is" Greater than =TODAY()+14
    yellow format

    condition 3
    "cell value is" Greater than =TODAY()
    green format

  3. #3
    Corey
    Guest

    Re: Conditional Format based on date ranges

    Thanks for the reply.
    I have entered as you suggested, but i do not get ANY shading at all??


    --
    Regards

    Corey

    "daddylonglegs" <daddylonglegs.2bs6qv_1154301011.7433@excelforum-nospam.com>
    wrote in message
    news:daddylonglegs.2bs6qv_1154301011.7433@excelforum-nospam.com...
    >
    > The order in which you apply the conditions is important. You can use
    > "formula is" but "cell value is" works fine too. Use something like
    > this
    >
    > condition 1
    > "cell value is" Greater than =TODAY()+30
    > red format
    >
    > condition 2
    > "cell value is" Greater than =TODAY()+14
    > yellow format
    >
    > condition 3
    > "cell value is" Greater than =TODAY()
    > green format
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile:
    > http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=566469
    >




  4. #4
    WLMPilot
    Guest

    RE: Conditional Format based on date ranges

    Here are the formulas you need. I made a couple of adjustments. Day 14 & 15
    get overlooked according to your formulas and if that is what you want, then
    adjust the formulas I send you. I am using A1 as the cell with the date you
    want to check:

    1st) =AND(A1<>"", A1>TODAY(), A1<=TODAY()+14)
    2nd) =AND(A1<>"", A1>TODAY()+14, A1<=TODAY()+30)
    3rd) =AND(A1<>"", A1<=TODAY())

    "Corey" wrote:

    > I am trying to set the following to have conditional formatting:
    >
    > >Current date < Current date+14 days = Green shade
    > >Current date+15<Currentdate+30=Yellow shade

    > <=Current date=Red shade
    >
    > I am having trouble with the Between formulas.
    > I have tried something like:
    > Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade
    > Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade
    > Cell value is GREATER THAN: [NOW()+31] Red shade
    >
    > But i get no CF?
    >
    > The values ARE a calculated DATE value, Does this mean i should use the
    > FORMULA IS instead of the Cell is?
    >
    > I then ONLY get Value is and Not the option of IS BETWEEN....
    >
    >
    > Any ideas
    > i am sure it is a simple formula sintax error i am doing wrong
    > Regards
    >
    > Corey
    >
    >
    >


  5. #5
    DonCam65
    Guest

    Re: Conditional Format based on date ranges

    The following works for me
    Select the first cell (calculated or not - it makes no difference) for the
    conditional formatting

    Format
    Conditional Formatting
    Condition 1
    <Cell Value> between =now()-1 and =now()+14
    format to required green
    Add condition 2
    <Cell Value> between =now()+15 and =now()+30
    format to required yellow
    Add condition 3
    <Cell Value> greater than =now()+30
    format to required red

    (Note that in the conditions there are no spaces)

    OK
    Click on FORMAT PAINTER
    Highlight all the cells the formatting is to apply to

    Hope this solves your problem

    Don C


    "Corey" wrote:

    > Thanks for the reply.
    > I have entered as you suggested, but i do not get ANY shading at all??
    >
    >
    > --
    > Regards
    >
    > Corey
    >
    > "daddylonglegs" <daddylonglegs.2bs6qv_1154301011.7433@excelforum-nospam.com>
    > wrote in message
    > news:daddylonglegs.2bs6qv_1154301011.7433@excelforum-nospam.com...
    > >
    > > The order in which you apply the conditions is important. You can use
    > > "formula is" but "cell value is" works fine too. Use something like
    > > this
    > >
    > > condition 1
    > > "cell value is" Greater than =TODAY()+30
    > > red format
    > >
    > > condition 2
    > > "cell value is" Greater than =TODAY()+14
    > > yellow format
    > >
    > > condition 3
    > > "cell value is" Greater than =TODAY()
    > > green format
    > >
    > >
    > > --
    > > daddylonglegs
    > > ------------------------------------------------------------------------
    > > daddylonglegs's Profile:
    > > http://www.excelforum.com/member.php...o&userid=30486
    > > View this thread: http://www.excelforum.com/showthread...hreadid=566469
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1