Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional Formats (XL2003)

    I am trying to apply shading to a cell (B2) if the time in (C2) is greater than 3:00. I've used conditional formatting this way: Formula Is...=(C2>3:00). This resulted in a formula error while still in the Conditional Formatting dialog.

    So, I removed the first equal sign: Formula is...C2>3:00 and the formula was accepted, but does not produce the expected results. Looking back into the Conditional Formatting for B2, it has changed the formula to: "C2>3:00" with the """ marks!

    I need help with the conditional formatting formula for B2 and it needs to be one that can be copied to other cells. For example, I need the same formatting applied many rows down the B column. The shading will be applied if the cell to the immediate right is greater than 3:00. The cell format applied to the C column is a custom format... <font color=blue>h:mm</font color=blue> (if that matters).
    - Ricky

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

    Re: Conditional Formats (XL2003)

    Use this formula:

    =(C2>TIME(3,0,0))

    Or put the time value 3:00 in a cell, say P37, and refer to that:

    =(C2>$P$37)

    Note that the reference to C2 is relative, so that it will adapt itself in other cells, while that to P37 is absolute, so that it won't change.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formats (XL2003)

    I used your first recommendation and it works great. I do have a follow-up question if I may...

    I did not try the 2nd option you offered but it does make me curious why it would work... If $P$37 contains the time value 3:00 then it seems the two formulas below would produce the same results:
    <pre>=(C2>$P$37)
    =(C2>3:00)</pre>


    Other than half a brain, what is it that I'm missing here! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    - Ricky

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

    Re: Conditional Formats (XL2003)

    3:00 is not recognized in a formula, but a reference to a cell containing 3:00 works fine. To include a literal time in a formula, use TIME(hour, minute, second) or TIMEVALUE("timestring").
    Similarly, you cannot use a literal date in a formula directly, it would be interpreted as a division. Use DATE or DATEVALUE instead.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional Formatting (Pt. 2)

    I've had to change my layout a bit and it has created a new conditional formatting challenge..

    On a sheet, I have 35 separate 2C X 4R groups of cells. In the first column is a dollar amount and the adjacent column has a time (h:mm). Previously, I was applying a conditional format when the time exceeded another certain time... Now, what I need to do is apply the format to the highest time within it's 2C X 4R grouping. The attached diagram shows what the end result should look like.

    If one such group of cells is A1:B4... I could use the formula =MAX(B1:B4) in some faraway cell (N63). Then apply conditional formatting to each of the cells in B1:B4 - (<font color=blue>Cell Value is Equal to =N63</font color=blue>. But there must be a better way? Using 35 faraway cells seems like the hard way.
    - Ricky

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

    Re: Conditional Formatting (Pt. 2)

    Say that one of your groups is D6:E9. You want to shade the cells in D and E for which the time in E is the highest within this block.
    Select D6:E9.
    Select Format | Conditional Formatting...
    Select Formula Is from the dropdown list.
    Enter the formula
    =($E6=MAX($E$6:$E$9))
    (note the use of absolute and relative references)
    Click Format... and set the desired shading.
    Click OK twice.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Pt. 2)

    Awesome! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Pt. 2)

    In addition to Han's solution, you can also "coerce" text time/date.

    =c2>"3:00"+0

Posting Permissions

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