Excel Frustration

[Crispin]

Actually, Excel is brilliant, frustrated that I cannot work it out.

Have a spreadsheet that have applied a conditional formating rule as follows:

'Format only cells that contain' : Blanks (so that uncompleted cells can be quickly identified with shading)

The spreadsheet has 2 date columns that I wish to highlight the second date with red infill only if it is still blank after 4 days or if the date entered is after 4 days.

Have been trying the following, however this is clearly inadequate conditions to stop all in second date row eventually turn red or indeed any without a date in first column will also turn red: 'use a formula to determine which cells to format' : =TODAY()-$A2>4 (then format to fill cell red)

Grateful for any help!

[Alansmithee]

I'm curious how correct Chatgpt is to this problem?

For the conditional formatting rule you want to implement, it seems like you need to satisfy two conditions:

1. The cell in the second date column should be blank.
2. The first date in column A should be at least 4 days ago.

You can combine these two conditions using an AND function in Excel's conditional formatting.
Assuming your first date column is Column A and your second date column is Column B, the formula in conditional formatting would look something like this:

=AND(ISBLANK(�2),A2<TODAY()-4)=AND(ISBLANK(B2),A2<TODAY()-4)

Here's how this formula works:

• ISBLANK($B2) checks if the cell in the second date column (B2 in this example) is blank.
• TODAY()-4 calculates the date 4 days ago.
• $A2<TODAY()-4 checks if the date in the first column (A2 in this example) is at least 4 days ago.

By using AND, you ensure that both conditions must be met for the cell to be formatted.
Steps to apply this conditional formatting:

1. Select the range of cells in your second date column where you want to apply the conditional formatting.
2. Go to Home -> Conditional Formatting -> New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the formula =AND(ISBLANK($B2), $A2<TODAY()-4) in the formula field.
5. Click on Format, choose the red fill color, and click OK.
6. Click OK again to apply the conditional formatting rule.

Now, the cells in the second date column should turn red only if they are blank and the corresponding cell in the first date column is at least 4 days old.

[Mr Pointy]

The spreadsheet has 2 date columns that I wish to highlight the second date with red infill only if it is still blank after 4 days or if the date entered is after 4 days.

Within 4 days of when? If you use TODAY then of course all cells will eventually turn red as the entered date is 4 days earlier than today.

[Kingstepper]

Not 100% sure what you’re trying to achieve either TBH but try something like this?

[Crispin]

Thanks for all the detailed advice, not sure I have got it right yet!

Trying to get column C to infill red where a date is entered in column A, and where either no date has been entered in column C within 4 days or the date entered is over 4 days (but not to infill if date entered in C is <4 days).

Also, the above rules should not apply where no entry is made in column A (a separate rule fills this grey if blank entry)

[Mr Pointy]

One way is to create a Helper Cell which holds the difference between the dates. You can then use a simple formula in the Conditional Formatting rule:

Excel CF 1.jpg
Excel CF 2.jpg

Note you can run both rules at the same time, just set the greying out one as the first rule & tick the box to 'stop if true'.

If you don't want to use helper cells then you can embed the calculation in the CF rule but it can become convoluted.

Also note when you create the Red rule that Excel will default to an absolute $D$4 format which you need to change to the relative version D4.

Edit: I've noticed there's an issue if no checked date is entered, I'll look at that later.

[Mr Pointy]

I had to add another rule that first checks if the Checked By cell is empty:

Excel CF 3.jpg

It's orange to show the difference.

[Crispin]

Thanks, thats really helpful, will give that a try