Conditional Formatting Help with Dates

[JHaugland]

Two date columns. A & B.

I want the column B to fill red if the date in column A is greater than 4 days.
I want the column to turn green if if the date is less than 4 days.

Help please

 

[trimmjens]

Try this.

1. Select cell B1 and go to Conditional Formatting --> New Rule
2. Format only cells that contain
3. Choose Cell Value - "less than or equal to"
4. Type in this formula: =$A$1-4
5. Format the cell to fill red
6. Press ok

Repeat this steps only in step 3 you select "greater than og equal to".
Step 4 you change the formula to: =$A$1+4.
Format the cell fill to green.

See what happens when you change the date in A1.


Thomas

 

[Joe4]

Since you are working with "dates" and not "numbers", what exactly do you mean by "greater than 4 days" or "less than 4 days"?
Are you talking about the difference between the two dates? If so, which one is the bigger date?
If not, then what exactly do you mean?

 

[JHaugland]

Since you are working with "dates" and not "numbers", what exactly do you mean by "greater than 4 days" or "less than 4 days"?
Are you talking about the difference between the two dates? If so, which one is the bigger date?
If not, then what exactly do you mean?

Yes, the difference between two dates.

[TABLE="width: 136"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2018 [/TD]
[TD="align: right"] 9/18/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2018[/TD]
[TD="align: right"] 9/28/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Just select the cells in column B that you want to apply it to (I am assuming there is a header row, so let's use row 2), and enter the following Conditional Formatting formulas:

Condition 1
Formula: =$B2-$A2 > 4
Format color: red

Condition 2
Formula: =$B2-$A2 < 4
Format color: green

NOTE: If it is possible that one of these dates may not be populated, we may need to account for that, depending on what your requirements are.
If you only want the Conditional Formatting to be applied if both dates are populated, you could do it like this (for Condition 1):
=AND($A2 > 0,$B2 > 0,$B2-$A2 > 4)
(Condition 2 would follow similar structure logic)