conditional formatting

[tekjock]

I have a conditional format set up to change the color of the cell based on the date in the cell. How can I make the 3rd condition set the cell to no color if the cell is empty?

[NBVC]

If you're playing with dates, you may need to include a condition for if the cell is >0 and whatever your date conditions are e.g. =And(A1>0,date condition).

Excel sees blank cells as equivalent to 0 when using conditional formatting.

Perhaps you can post your current conditions and what exactly you want to achieve. Then we may better help.

[starguy]

I have a conditional format set up to change the color of the cell based on the date in the cell. How can I make the 3rd condition set the cell to no color if the cell is empty?

or simply add 3rd condition (although not a professional approach)

Cell value is: equal to: =""
and choose colour

[tekjock]

If you're playing with dates, you may need to include a condition for if the cell is >0 and whatever your date conditions are e.g. =And(A1>0,date condition).

Excel sees blank cells as equivalent to 0 when using conditional formatting.

Perhaps you can post your current conditions and what exactly you want to achieve. Then we may better help.

I have 2 conditions set adn they work great, I just need a 3rd condition to say that when the cell is empty have it set to no color

or simply add 3rd condition (although not a professional approach)

Cell value is: equal to: =""
and choose colour

I think I tried that, but will again

Thanks guys
Tek

[starguy]

I have 2 conditions set adn they work great, I just need a 3rd condition to say that when the cell is empty have it set to no color



I think I tried that, but will again

Thanks guys
Tek

this approach is valid if need not more conditional formats. however if you need (may be in future) more conditions you can use OR function nested in IF function to return the same results with 3rd condition still available for use.

[tekjock]

Ok the ="" is not working ... I will show you what I am trying to do and show you what I got.

I want to be able to put a date in a cell, and then in 5 days I want the cell to change color. For example, I put a date in a cell ( March 19, 1007 ) and the cell is Red, then when I open the file on Saturday the 24 the cell changes to green. Now this part I have working by doing the following.

Condition 1
[Cell Value Is] [less than or equal to] [=TODAY()-5]
Green BGD + Yellow txt
Condition 2
[Cell Value Is] [Greater than or equal to] [=TODAY()-5]
Red BGD + Black txt

Now all I need is a 3rd Condition to make a blank cell have no color

thanks
Tek

[starguy]

Ok the ="" is not working ... I will show you what I am trying to do and show you what I got.

I want to be able to put a date in a cell, and then in 5 days I want the cell to change color. For example, I put a date in a cell ( March 19, 1007 ) and the cell is Red, then when I open the file on Saturday the 24 the cell changes to green. Now this part I have working by doing the following.

Condition 1
[Cell Value Is] [less than or equal to] [=TODAY()-5]
Green BGD + Yellow txt
Condition 2
[Cell Value Is] [Greater than or equal to] [=TODAY()-5]
Red BGD + Black txt

Now all I need is a 3rd Condition to make a blank cell have no color

thanks
Tek

do only two conditions you use work properly???

try following conditions

select the range (I suppose it is A1:A100)

Condition 1
Formula Is =AND(A1<>"",A1<=TODAY()-5)
Green BGD + Yellow txt
Condition 2
Formula Is =AND(A1<>"",A1>TODAY()-5)
Red BGD + Black txt

[tekjock]

do only two conditions you use work properly???

try following conditions

select the range (I suppose it is A1:A100)

Condition 1
Formula Is =AND(A1<>"",A1<=TODAY()-5)
Green BGD + Yellow txt
Condition 2
Formula Is =AND(A1<>"",A1>TODAY()-5)
Red BGD + Black txt

thank you sir, that works perfectly

tek

[starguy]

thank you sir, that works perfectly

tek

pleased to know that it solved your problem
and thank you for the feedback.

[NBVC]

do only two conditions you use work properly???

try following conditions

select the range (I suppose it is A1:A100)

Condition 1
Formula Is =AND(A1<>"",A1<=TODAY()-5)
Green BGD + Yellow txt
Condition 2
Formula Is =AND(A1<>"",A1>TODAY()-5)
Red BGD + Black txt

This is what I was trying to point out too.

[starguy]

This is what I was trying to point out too.

you were right at this point.

[tekjock]

This is what I was trying to point out too.

Sorry but i'm not the best at excel formulas, but I do appreciate the assistance .

thank you

[tekjock]

Ok, like i said this works perfectly ... BUT ... is there a way to do the same thing , but make it 5 business days. So when I counts how many days, it excludes the weekends.

I could allways just edit those cells in question to 7 days.

is this possible WITHOUT VB scripting

thanks
Tekjock

[NBVC]

Try:

Condition 1:
Formula Is >

=AND(A1<>"",A1<=TODAY()+SIGN(-5)*SMALL(IF((WEEKDAY(TODAY()+SIGN(-5)*(ROW(INDIRECT("1:"&ABS(-5)*10))),2)<6),ROW(INDIRECT("1:"&ABS(-5)*10))),ABS(-5)))


Condition 2:
Formula Is >

=AND(A1<>"",A1>TODAY()+SIGN(-5)*SMALL(IF((WEEKDAY(TODAY()+SIGN(-5)*(ROW(INDIRECT("1:"&ABS(-5)*10))),2)<6),ROW(INDIRECT("1:"&ABS(-5)*10))),ABS(-5)))

[tekjock]

That works ...

BUT ...

I actuly solved this a bit simpler. changed my thing above

From :
=AND(Q1<>"",Q1<=TODAY()-5)
To :
=AND(Q1<>"",Q1<=TODAY()-7)

the changing from -5 to -7 compensated for the 2 days.

thank you for all your help
Tekjock

[NBVC]

You're welcome...

Actually, my formulas actually account for business days...so they will always exclude Saturdays and Sundays no matter what Today() is.

With your formula you are just subtracting 7 days from Today() which may end up falling on a weekend. But I guess if you are only looking at this spreadsheet during Monday to Friday, then you're formulas will be good enough. If you open the sheet on a weekend, you may not get expected results.