+ Reply to Thread
Results 1 to 8 of 8

Conditional format formula

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Conditional format formula

    Hi,
    I am trying to create a conditional format for my excel sheet office 2010, If anyone can help to set it up. I have a date in cell J1 (01-01-2014), I want that cell J1 stay green first year change to yellow after second year and change red after 3 years.

    Or if there is way to change the colour of row A1:H1 to yellow when it past two year and red after 3 years.

    Thanks in advance,
    RR

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Conditional format formula

    So for the green formatting use this formula in conditional formatting
    =TODAY()<EDATE(J1,12)

    For the yellow formatting use this:
    =AND(TODAY()>=EDATE(J1,12)TODAY()<EDATE(J1,24))

    And for the red:
    =TODAY()>=EDATE(J1,24)
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: Conditional format formula

    Thanks gak67,

    The yellow colour formula did not work for some reason, it is giving me a error.

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Conditional format formula

    I left out a comma. Try this instead: =AND(TODAY()>=EDATE(J1,12),TODAY()<EDATE(J1,24))

  5. #5
    Registered User
    Join Date
    10-01-2014
    Location
    USA
    MS-Off Ver
    2011
    Posts
    2

    Re: Conditional format formula

    Wow! Thank you for posting this question and the very helpful reply.

    I was wondering if it's possible to do the same thing, but instead of changing the cell color, use an "Icon Set" as the indicator. Thanks for any help you can offer!

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Conditional format formula

    Hi ocn2mtn,
    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    10-01-2014
    Location
    USA
    MS-Off Ver
    2011
    Posts
    2

    Re: Conditional format formula

    Hi sktneer, thank you for pointing that out. I'll review the rules again too before posting!

  8. #8
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: Conditional format formula

    Thank you Gak67 for your time, It works this time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula/format base don another conditional format
    By ketteringjack in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-12-2012, 07:35 AM
  2. [SOLVED] Conditional format with a formula
    By Rod (Brazil) in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 AM
  3. Conditional format with a formula
    By Rod (Brazil) in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Conditional format with a formula
    By Mike Echo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. Conditional format with a formula
    By Mike Echo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-19-2005, 07:05 AM

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