Conditional Formatting Adjacent Cells

[lulaB]

So i have my initial formats done:

Column I: Planned date
Column J: Revised date (if needed)
Column K: Actual Date

Both I and J have a format to turn colors based on how far we are for the dates.. (ex: 5/15/30 days)

My issue, if there has been a revised date in J, i'd like I to turn white- and once an ACTUAL date posted in K I'd like for J (or original date I) to turn white. SO the only dates colored are past due and up coming (ie not completed) I am stuck.. help?

 

[lulaB]

Maybe this will help with what I'm talking about

A B C
10/16/2017
12/1/2017
7/28/2017 10/31/2017
9/1/2017 12/1/2017
1/26/2018 3/7/2018
2/2/2018 3/21/2018
9/22/2017 1/17/2018
11/17/2017 1/24/2018
7/28/2017 10/31/2017 10/31/2017
9/1/2017 12/5/2017


Need Column A to turn white if a date is in the column B, and if date is in column C – Column A and B should be white

 

[Joe4]

So, are you just looking to check to see if both columns B and C are populated with dates?
If so, just use a Conditional Formatting formula like this:

=AND(B2>0,C2>0)

 

[lulaB]

no, So column A is "Planned date" and B is "updated date" so if the date has been updated from the planned date (A) i want the planned date to turn white so you only focus on the color in the 'updated date" or column B

 

[Joe4]

Sorry, I am afraid your explanations are no very clear.
Let's walk through each situation, and you can tell us what should happen in each situation.

1. Date entered in column A only
2. Dates entered in column A and column B only
3. Dates entered in columns A, B, and C

 

[lulaB]

dates A- Original planned delivery date

Date B- UPDATED delivery date

Date C- Delivered date[TABLE="width: 500"]
<tbody>[TR]
[TD]A Planned[/TD]
[TD]B update[/TD]
[TD]C delivered[/TD]
[/TR]
[TR]
[TD]12/1/2017[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7/28/2017[/TD]
[TD]3/31/2018[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7/28/2017[/TD]
[TD]9/20/2017[/TD]
[TD]10/1/2017[/TD]
[/TR]
[TR]
[TD]1/26/2018[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

I currently have the CF that changes the color of the boxes as i get withing a week..month..3 months of the date.

 

[Joe4]

You still haven't answered my question.
Remember, while this problem is very familiar to you, all that we have to go on is the little that you have posted here.
You haven't even posted your current rules.

So based on the example that you posted in your last post, walk us through each instance, and let us know what you want to happen in each one (and the logic behind it).

 

[lulaB]

OH! I am sorry!

Column A is just dates and the CF changes the color based on how close we are getting to the due date or if we are past the date ( graded color scale applies to =$A:$A.... Formula Min=today()+15 (RED); Mid =Today()+30; Max =Today()+60)

Column B- the updated date, same CF- turns green-yellow-red based on distance to the date or past date. ( graded color scale applies to =$A:$A.... Formula Min=today()+15 (RED); Mid =Today()+30; Max =Today()+60)
IF there is an updated date in B- THEN column A turns white and the CF in B is colored

Column C- item delivered Formula =$C:$C <> format- Bold green text)


What i am trying to do...

IF there is an updated date in B- THEN column A turns white and the CF in B is colored

ONCE the actual date is filled in both column A and B are white.


the reason- I want to make it 'clean' meaning your eye is drawn to the most active date in that row- whether that be A (only date) B (the updated date) or C (the delivered date).



does that help? Im sorry about not being clear before.

 

[Joe4]

OK, I think you will need to change over how you are doing Conditional Formatting, and use the formula options for all.
So, you would probably use a lot of ANDs to make sure they meet all your criteria.
For example, to check to see if the value in A2 is within the past 15 days, and columns B and C are blank, the CF formula would look like:

=AND(B2="",C2="",A2>(TODAY()-15),A2 < TODAY())

<today())[ code]<="" html=""></today())[>

 

[lulaB]

ok THANKS! I'll try it out!