#  Other Applications & Softwares  > Outlook Formatting & Functions >  > [SOLVED] Conditional format where cell value is a past date

## helmerr

I have a table that lists the every person in the company and the date they completed their last annual certification. 
There are multiple annual certifications for each person. 
The  table looks something like following:
LastName, FirstName, Cert01, Cert01, Cert03, Comments
Johnson, Jack, 06/12/15, 01/30/15, 01/01/15, Comment

All dates are in the past. All certifications are annual.
I need to fill the cell of each certification date (the original cell) 3 months prior to their next annual, up until it will be due (which is 1 year from the original date). 
Sorry, I get confused when trying to write the formula out...

----------


## etaf

you can use conditional formatting to fill the date when it is 3months to todays() date 

use a formula 
=cell with date in < DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

assuming your data is in C2:E200
select that range and then use 
= C2 < DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))


for 2007, 2010 or 2013 excel version
* Conditional Formatting* 

Highlight applicable range >> 
 
C2:E200


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true: 


= C2 < DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK


Otherwise
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a *manual mock up of the expected results you want to achieve*.

To attach a file to your post, 
click "Go advanced" (next to quick post), 
scroll down until you see "manage Attachments", 
click that and select "add files" (top right corner). 
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window. 
You can then close the window to return to the new post screen.

----------


## helmerr

That worked perfectly. Your formal looks way more complicated than what I was trying to do (which is probably why it didn't work.)
Thanks!!

----------


## etaf

I usually use the DATE() function - which does look complicated 
but all its doing is finding the date() from the cell 
using year(), month() , day() 
and then i subtract the number from one of those items 
OR you could just take of 90days , if you not worried about accuracy, ie feb has less days etc 

= C2 < today() -90

----------

