Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Conditional formatting - multiple conditions (Exce

    I am trying to figure out how to set up some conditional formatting, as follows...

    I am setting up an issue/action item tracking spreadsheet for an office move. If an issue/action item has not been completed before its due date, then I want the due date to become red and bold. As I see it, there are three variables involved

    Status - <font color=red>new - assigned</font color=red> - complete
    Date Due - <Today(), - =Today() - <font color=red>>Today()</font color=red>
    Date Completed - >Due Date - =Due Date - <font color=red>>Due Date</font color=red>

    In my estimation, the red conditions above are when I want the due date to turn <font color=red>red</font color=red>.

    I am having problems figuring out the logic under the Format>Conditional formatting and need another set (or more) of eyes on this problem. <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Can anyone tell if the three conditions that you can set under the conditional formatting are AND conditions or OR conditions? I suspect they are OR conditions, and I suspect my conditions are AND conditions. Another questions is - how complex an expression can you have in these conditional formatting situations?

    Any help, or insight, that anyone can provide is greatly appreciated. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting - multiple conditions (Exce

    The three conditions you can set in Conditional Formatting are meant to be mutually exclusive:
    - if condition 1 is true, set format 1
    - if condition 2 is true, set format 2
    - if condition 3 is true, set format 3

    By selecting Formula Is in the first dropdown list, you can use a complex formula as a condition. The formula must not exceed 256 characters in length; if you need a more complex formula, you can put it in another cell, and refer to that.

    Your formula would look like this, where the names can either be named ranges, or should be replaced by cell references:

    =AND(OR(Status="new",Status="assigned"),DateDue>TO DAY(),DateCompleted>DueDate)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional formatting - multiple conditions (Exce

    I have assumed that the status is in column A rows 2 thru whatever (row 1 is header)
    COl b has Due date
    Col C has date completed

    Select the cells B2 to B whatever
    Format- Conditional format
    "Formula is":
    <pre>=AND(OR(A2="new",A2="assigned"),B2>TODAY(),C2 >B2)</pre>

    <format>
    [change format of patter/text as desired'
    <ok><ok>

    I question the conditions you ask for though. If you want to mark when the date is PAST due, don't you want B2<Today()? (the due date earlier than today to be late)
    Also if the task is NOT complete and it has no due date but the other conditions are met do you still want it marked?

    I am thinking you want this as a condition (if I understand your "goal", not your "request"):
    <pre>=AND(OR(A2="new",A2="assigned"),B2<TODAY(),OR (C2>B2,ISBLANK(C2)))</pre>


    The 3 conditions are not AND nor OR. They are mutually exclusive.

    If Cond 1 is true, it sets Cond1 formatting. It does not matter what the other 2 conditions are
    If cond 1 is false and cond 2 is true then it does cond 2 formatting and cond 3 is immaterial
    if cond 1 and 2 are both false and cond3 is true then it does cond 3 formatting
    if all 3 are false, then the explict formatting of the cell is used.

    If you want multiple conditions you must put them in a formula with ANDs and ORs.
    Steve

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Conditional formatting - multiple conditions (

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thanks Hans and Steve. I will follow your lead on this. You have confirmed my suspicions about the three conditions. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Ron <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    P.S. Steve, you were right about conditions - a typo on my part. Sorry if this caused some confusion. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Posting Permissions

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