Date reminder 7 days before

[Sjokwaave]

Hi all.

I have a spreadsheet with peoples details I need to contact once every month over a 12 month period. I have the dates across twelve columns for when they are due contact in that month. Is there anyway to have excel remind me 7 days before, preferably by highlighting the relevant cell? I've tried conditional formatting, but most of the help that I've found tends to be on based on the 'TODAY' value or creating a separate table rather than the date in the cell.

Any help would be appreciated.

Cheers,

Sjok.

 

[awoohaw]

If you are not going to use the TODAY() function, how are you creating a reference date to compare the due dates?

 

[Sjokwaave]

You'll have to forgive me, because my knowledge of excel is not great.

I want the reference date to be the date in the cell. I tried using =TODAY()-7 (that I got trying to google a solution, which all seemed to be around accounting which is not what this is for) but that ending up highlighting all future dates.

Let's say that the cell says 22/01/2024 as the date I'm due to make contact - I'd like the sheet to turn the cell red 7 days before.

 

[awoohaw]

If the date 22/01/2024 is in cell B2, then the Conditional Formatting for cell B2 should be:

=(B2-7)<=TODAY()

then copy formatting down using the format painter.

 

[Sjokwaave]

Thank you very much, solved it!

 

[awoohaw]

Thank you very much, solved it!

Happy to help.
Best Wishes!!