Checking dates with Excel conditional formatting

Question

I am making a document to keep track of things that happen on a weekly basis and I want to implement conditional formatting as a way to make sure I catch user errors. I have one column with the start date and one with the end date, which will normally be 6 days away from the start because it is 1 week. I want the cell to turn red if I input a value that does not fit this assumption.

Example table:

In the 'Edit Formatting Rule' menu I have tried:

Format only cells that contain | Cell Value | not equal to | =($A2+6) |

but it just made everything red.

Message I get on Mac if I enter 24/12/2019 in A1 with the above Data Validation added i.stack.imgur.com/MnIjO.png i.stack.imgur.com/HgpOf.png (In UK where Date format is DD/MM/YYYY) — JGFMK
JGFMK I think that would have the same issue as before, only it doesn't let the user input anything. I think the problem is that the logic in cell value = $A2+6 is somehow failing. — Jelly Joe
if you link some sample data perhaps it can be fixed. It may be that DATE() function will allow +6, but perhaps not a string literal. Put a sample in Googledocs, and link it. You can upload/download from/to Excel — JGFMK
With the correct logic the solution on http:excelchamps.com/blog/data-validation-date-range does an extreme version of what I wanted. Thanks! — Jelly Joe

Jelly Joe Jelly Joe · Accepted Answer · 2019-12-26T18:15:29

Oh I found it out. The problem was in $A2. apparently you have to use $A1 to get to the same row. Would this mean Excel uses cell names differently in conditional format than normal cells?

Checking dates with Excel conditional formatting

1 Answers