+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting with colors based on date value

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    2

    Conditional Formatting with colors based on date value

    Hi All,

    I've a worksheet with 3 columns namely Task, Status & Start Date.
    Task column contains various tasks.
    Status column has a Data Validation list containing 3 different statuses 'Open', 'In-Progress' and 'Completed'
    Start Date column contains a date.

    My requirement is to color the cells in Status columns based on the selected status. I've used conditional formatting to do it as below.
    If 'Completed' status is selected, the cell is colored 'Green'
    If 'In-Progress' status is selected, the cell is colored 'Yellow'

    However I'm unable to get the below requirement.
    My requirement for 'Open' status is as follows:
    If the date in Start Date column of the corresponding row having 'Open' status is greater than today, then the color would be 'No Color'.
    If the date in Start Date column of the corresponding row having 'Open' status is lessthan today, then the color would be 'Orange'.

    Any help in this regard is appreciated. Thank you.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting with colors based on date value

    =AND(CELL OF THE STATUS COLUMN="OPEN",CELL OF THE START DATE COLUMN<TODAY())

    This is the condition for Orange color.

    For No color you don't need any condition.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    03-28-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Conditional Formatting with colors based on date value

    It worked. Thank you

+ 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