Two conditions for same format??

  • Thread starter Thread starter christopherp
  • Start date Start date
C

christopherp

Hi Guys,

I have a number of cells conditionally formatted with three differen
conditions. (all cell values relate to the number of days left befor
an assignment is due).

The number of days left is calculated with the following formula:

=IF((G7-TODAY())<=0,"Complete",G7-TODAY())

at the moment:

1st format is applied when cell value <=30
2nd format is applied when the cell<=10
3rd and final format is applied when the cell value <=0 (cell display
"complete")

What I would like to do is make the cell display "today" when there ar
exactly 0 days left and then display "complete" when there are less tha
0 days left by amending the formula to read:

=IF((G7-TODAY())=0,"Today",IF(G7-TODAY()<0,"Complete",G7-TODAY()))

The new formula works fine, however, I would also like the cell to b
formatted the same as it is there are <=10 days left

As I have used all three conditions allowed in Excel XP already, thi
is proving difficult.

I hope I have explained this sufficently well for someone to hel
:rolleyes:

Any suggestions???

Thanks in advance

Chri
 
For your 3rd format, choose "Formula Is" rather than "Cell Value Is"
and try this formula:

=OR(A1="Today",A1="Complete")

You need to change the A1 reference to the cell reference for which the
CF applies.

Hope this helps.

Pete
 
I thought I would have gotten at least one response !! :confused:

Does anybody have a suggestion?... anybody
 
If you have the formatting the way you describe, with <=30 as the first
condition then that won't work because when you have a value of 8, for
instance, that will trigger the first condition and the second will be
ignored, suggest you reverse the order and use "formula is"

condition 1

=$G$7<today()

condition 2

=$G$7-today()<=10

condition 3

=$G$7-today()<=30
 
I appreciate what you are saying and it is actually setup that way and
it works just the way I want it to.

I do not need the conditional formatting formula to work out the value
of the cell because that is done using this formula

=IF((G8-TODAY())<=0,"Complete",G8-TODAY())

All I want to do is apply the same formatting to the cell when the
value meets one of two criteria:

criteria 1: value is <=10
criteria 2: value ="today"

I am struggling to do this because I have already used all three
conditions for formatting.

Can you use IF(OR( formulas in cond formatting ?
 
You have formulas in one cell based on the value of another so the
conditional formatting can be based on the value of either, it seemed
to me to be easier to base the formatting on the value of the initial
cell but you could use the other approach by using an OR formula with
the "formula is" option within conditional formatting

You don't say which cell is being formatted but assuming H7 then you
could use

=OR(H7="today",H7<=10)

...however, my previous point stands. If you make this condition 2 and
have a condition 1 which formats values <= 30 then the condition 2 will
only ever be triggered when H7 is "today"
 
Back
Top