Let’s say I have an Excel spreadsheet that has a list of team member names and their last training date certifications. I would like to make it easy to determine who has a certification that is over one year old. I can use Excel conditional formatting to make the date turn red if the date is over a year old and that person need to become re-certified. Ok, first let’s look at the screen shot in which I have a simple list of names in column A and dates in column B. My first date of my list begins in B2.
I start out by clicking on my first date cell. Then I click the Conditional Formatting command button–>New Rule.
I then click “Use a formula to determine which cells to format.” If I start out by clicking my mouse on my first date B2 it will automatically write =$B$2. I need to modify the absolute referencing to make it just say =B2 since I want to copy the formula down. My final formula reads =B2<=TODAY()-365. The TODAY() function always returns today’s date. The 365 is for subtracting a year ago to compare if the date is older than 1 year. I then click Format to choose red fill color. After I click OK I can verify if it works.
Once the first cell has the date conditional formatting, I then need to use the format painter to copy and paste the format to the remaining date cells. I also could copy…paste special the B2 cell onto the other cells choosing the Formats only choice.
My final result ends up with this. 🙂
I have red cells for dates that are over 1 year ago. The dates that are within the last year do not show with red conditional formatting. But the nice thing about using the TODAY() formula is that is will always be updated when I open this file. If you would like to practice this lesson with my spreadsheet go ahead and click Conditional Formatting Dates over 1 year old.xlsx
Update as of Sept 19, 2014. I wrote a new post that covers this topic in more depth.