# Excel question - dates and conditional formatting

**URL:** <https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369>\
**Category:** Factual Questions\
**Created:** [July 22, 2008, 2:37pm UTC](https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369 "2008-07-22T14:37:46Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![samm](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@samm](https://boards.straightdope.com/u/samm)\
**Post date:** [July 22, 2008, 2:37pm UTC](https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369/1 "2008-07-22T14:37:46Z")

</div>

I keep a list of contracts in Excel and I would like Excel to tell me when a contract is within three months of expiring. I have all the contract end dates in there and I have used conditional formatting before, but I don’t know how to make it say “this date is less than or equal to three months from today’s date.” There must be a way for it to automatically highlight the end dates that are getting close?

---

<div class="post-metadata">

**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\
**Post date:** [July 22, 2008, 4:17pm UTC](https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369/2 "2008-07-22T16:17:34Z")

</div>

Which version of excel are you using?

All I have is 2007 and its conditional formatting may be different than 2003.

In excel 2007 I was able to get conditional formatting to work by applying a formula of

\< 90 + Now()

to the formatting formula for the cell .

Sometimes the process can mess up because Excel adds quotes before or after the expression when you initially set it up.

If that happen I usually try to remove the quotes via the edit button in “manage rules”

Sorry if this doesn’t apply to your Excel edition.

---

<div class="post-metadata">

**Author:** ![samm](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@samm](https://boards.straightdope.com/u/samm)\
**Post date:** [July 22, 2008, 5:31pm UTC](https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369/3 "2008-07-22T17:31:02Z")

</div>

Thanks BubbaDog! That worked!

---

<div class="post-metadata">

**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\
**Post date:** [July 22, 2008, 6:30pm UTC](https://boards.straightdope.com/t/excel-question-dates-and-conditional-formatting/457369/4 "2008-07-22T18:30:44Z")

</div>

One thing I forgot to mention Now() is a Date/time function.

Probably not a problem if your contract dates are dates only (whole numbers in Excel date/ time )

But if you have a contract in there entered as a date time number (like 10/23/2008 02:00 PM) (possible if you used a formula to set it like = Now()+365) then the formating won’t change until tomorrow at 2 pm.

Glad it worked  
Bubba