Contract Expiry Dates

[nwarren]

Hello,

In dire need of help!

I work at a company whom has many contracts with other companies and individuals. Each contract contains the effective date and expiration date. Recently we have run into the problem of using someones service who has an expired contract. We have no way of knowing when the contract expires unless we look at a hard/soft copy. We need a way to be notified when the contract is expired and when it is within 30 days of expiration.

So I created a tracker for these contracts and within it is, the vendor/party, effective date, and expiration. Now all i need was the reminder

I did some searching and discovered the conditional formatting. Worked like a charm! Then I realized it wasn't suiting our needs.

What we need:
For the cell to be highlighted when the expiration is within thirty days of the date marked in the cell, NOT within thirty days of current date.
AND For the cell to highlight when current date is the same date (or past current date) of the date marked in the cell.

For Example in the picture I have attached(if it is attached), you see in B2 the effective Date is Nov. 18 2014. This contract expires Nov. 18 2015. What I need to happen is thirty days before Nov. 18 2015, the cell automatically highlights itself blue so when we see this tracker lets say on Oct. 31 2015, we can see we are within thirty days of expiration and we can notify the contracted company for renewal.

Also take a look at B4. Effective date is Nov. 27 2007 the expiration date is Nov. 27 2010. The expiration Date has passed, I need that to automatically change to red after the current date is past the cell date.

I would greatly appreciate if anyone can tell me if what i need done is possible and can walk me through it.