PLEASE HELP! need conditional formatting related to date, blank cellsand today's date

[Michelle]

I want to create a conditional formmatting formula that will highlight
a cell if that cell is blank and another cell that has a date in it
is more than 30 days from today's date.

So, if J2 has a date in it and that date is greater than or equal to
30 days past today's date AND K2 is blank, I want to highlight K2 red.
If K3 is not blank I dont want it to be highlightd.

can anyone help me with the correct formula/conditional formatting?

Thanks so much!

 

[Pete_UK]

Try this in the Formula box of the CF dialogue:

=AND(K2="",J2-TODAY()>=30)

If J2 is in the past (not clear from your post), then use this:

=AND(K2="",TODAY()-J2>=30)

Then click the Format button and set your background colour to red,
then OK your way out.

Hope this helps.

Pete

 

[GS]

Michelle laid this down on his screen :

I want to create a conditional formmatting formula that will highlight
a cell if that cell is blank and another cell that has a date in it
is more than 30 days from today's date.

So, if J2 has a date in it and that date is greater than or equal to
30 days past today's date AND K2 is blank, I want to highlight K2 red.
If K3 is not blank I dont want it to be highlightd.

can anyone help me with the correct formula/conditional formatting?

Thanks so much!

C/F formula: =AND(J2>TODAY()+30,ISBLANK(K2))

 

[GS]

GS brought next idea :

Michelle laid this down on his screen :

C/F formula: =AND(J2>TODAY()+30,ISBLANK(K2))

oops! should be...

=AND(J2>=TODAY()+30,ISBLANK(K2))

 

[Michelle]

Pete - it is a date in the past! This works! Thanks so much!!!

 

[Michelle]

one more question - i forgot to tell you that I dont want k2 to be red
if j2 doesnt have a date in it. think that changes the
formula.....can you help again?

 

[GS]

Michelle expressed precisely :

one more question - i forgot to tell you that I dont want k2 to be red
if j2 doesnt have a date in it. think that changes the
formula.....can you help again?

Does this mean J2 would be empty OR have a value that is not a date?

 

[Michelle]

j2 would be empty. thanks!

 

[GS]

Michelle presented the following explanation :

j2 would be empty. thanks!

This should work in any case (empty OR value other than date)

=AND(SUM(J2)>=TODAY()+30,ISBLANK(K2))

 

[Michelle]

trying it now. Thank you!

 

[Michelle]

Garry - thank you. Quick question, when I test it using the date
1/1/2011 in J2, the cell does not turn red. I reversed the formula
(used less than trather than greater than, and the cell turned red, so
it would appear the formula as you wrote is correct......Do you think
it will only work going forward?

 

[GS]

Michelle submitted this idea :

Garry - thank you. Quick question, when I test it using the date
1/1/2011 in J2, the cell does not turn red. I reversed the formula
(used less than trather than greater than, and the cell turned red, so
it would appear the formula as you wrote is correct......Do you think
it will only work going forward?

Michelle,
The criteria you specified was to turn cell K2 red IF it was blank AND
the date entered in J2 was equal to OR greater than today's date. The
use of the TODAY() function means that this will adjust daily, meaning
that going forward the date in J2 will become less than today's date
and so K2 will not turn red.

 

[GS]

GS laid this down on his screen :

meaning that going forward the date in J2 will become less than today's date
and so K2 will not turn red.

This should read...

...meaning that going forward the date in J2 will become less than 30
days from today's date and so K2 will not turn red.

You could put a formula in K2 that calcs the number of days difference,
but then K2 won't be blank. If this was my project, I'd CF J2 to bold
red and put the following in K2:

=IF(SUM(J2)>=TODAY()+30,SUM(J2)-TODAY(),"")

Then change the CF formula (now for J2) to:

=SUM(J2)>=(TODAY()+30)

OR, if you want to know number of days dif between today and J2:

K2 formula: =IF(SUM(J2)>=TODAY(),SUM(J2)-TODAY(),"")

In either case, a result of '0' indicates that the dates match. Any
value greater than '0' is the number of days from today to the J2 date.
Otherwise, K2="" (empty string).

 

[Michelle]

perfect. Thank you again. You have been a HUGE help!

 

[GS]

Michelle brought next idea :

perfect. Thank you again. You have been a HUGE help!

You're welcome! Always glad to help...

 

[Zaidy036]

GS brought next idea :

oops! should be...

=AND(J2>=TODAY()+30,ISBLANK(K2))

It would be easier to read in the future if you separated the rules:
1. =ISBLANK(K2) no fill and check "stop if true"
2. =J2>=TODAY()+30 red fill

 

[GS]

Zaidy036 laid this down on his screen :

It would be easier to read in the future if you separated the rules:
1. =ISBLANK(K2) no fill and check "stop if true"
2. =J2>=TODAY()+30 red fill

Separating the rules doesn't work in CF (or any other formula use). It
might be better if you make the effort to interpret the application of
the formulas. The use of AND() includes both conditions apply. So the
correct interpretation of the CF formula is:

"if both J2=TODAY()+30, AND K2 is blank (empty) then red fill"

Anything else has no effect, and probably wouldn't work.