I have searched everywhere and cannot figure out what I'm missing. I have a column with contract expiration dates.
The neighboring column shows how many days' notice is needed if I want to extend the contract and inform the other party I'm doing so. In this column I would like to make conditional formatting to have a color fill if the current date is within the time period of the extension.
In the attached screenshot, I have the redilivery (expiration) date already formatted to be yellow if within a month of expiring, orange if within a week, red if past date.
I would like the days' notice in column K to also have a color notice. So If I need to send the notice for Row 5 10 days before the date in I5 (Oct 7) I would like a color fill to warn me if I'm within 7 days of that date (Sept 30 and later).
If needed as a reference, here is how I formatted column I. I am only going to show if the expiration date is within 7 days of today's date as the other formats use same code just different number.
Cell Value less than =NOW()+7, then formatted a fill of orange.
Here is what I tried to work in column K for the cell value, but I'm missing something, whether an absolute or relative reference, the order of the data, or what??
Cell Value less than =NOW()+$I1+7, format whatever fill color
Bookmarks