Date Formula

**JDPinard** · 03-04-2011, 01:36 PM

I am a moderate excel user and I am struggling with a formula.

I a creating a worksheet to track expiration dates of insurance policies for sub contractors that perform construction related work for us. I need to list the date that a policy expires and then have the policies that expire (without being updated) show differently. As an example:

If a policy expires on March 1st and it is now March 4th either the date would change to expired or the cell could be highlighted.

Does anyone have thoughts on how to set this up? Thanks in advance...

**bentleybob** · 03-04-2011, 01:49 PM

Conditional formatting (in Home Styles) would be perfect for this. Use a formula that simply compares the expiration date to TODAY(). If you need help setting this up, attach a sample spreadsheet.

**jwright650** · 03-04-2011, 01:54 PM

Like mentioned above, conditional formatting is the way to go....you can add several formatting rules if you like ....be green originally to show in good standing and turn yellow when it is a week from expiring, then turn red the day it expires and so on.

**JDPinard** · 03-07-2011, 01:18 PM

Thanks for the direction towards conditional formatting. I have played with it and am still struggling. I have a attached a quick sample of what I am doing. Could you be so kind as to enter the formula for me.

I can then copy & paste and complete the project.

Thanks...

**bentleybob** · 03-07-2011, 02:27 PM

You don't need to copy and paste because the "formula" is in Conditional Formatting. Go to the Home Styles ribbon and select Conditional Formatting, then Manage Rules, to see what is involved.

**JDPinard** · 03-07-2011, 03:11 PM

Thanks for the help. Changed the colors around a little bit and it worked perfectly!

Appreciate it!

**bentleybob** · 03-07-2011, 03:52 PM

You're welcome. And, as JW650 noted, you can get fancy and have different colors to warn of an impending expiration as well as the dreaded no-longer-covered.