Conditional Formatting for Expiration Dates

**knowledgeiskey** · 05-18-2010, 10:37 AM

Hello I am fairly new to Excel 2007 conditional formatting.

I have a Health and Safety database with dates of certification. Some of these certifications expire 1 year after completion, and some expire 3 years after completion.

What I would like to do is place conditional formatting in them to change the font to orange when they are to expire in 45 days. And then to change to red when they expire.

So this is what i had in the conditional formatting field, for the 1 year certifications:

=Today()-320, and i had the font color format as orange
and
=Today()-365, and the font color format as red.

but it doesn't seem to be working, I don't know which of these need to be "on top" and which needs to be on bottom? or if these are even the right codes to get me where i need to be. Please help! thank you!

I have attached my "test" file. I really appreciate it. oh btw I deleted my formatting, as it was a mess

.

**Richard Buttrey** · 05-18-2010, 11:09 AM

Hi,

I've added an additional row 1 where you can indicate whether these are 1 or 3 year courses.

I've put the conditional formats in C8 as an example. You just need to copy them to your other cells.

Incidentally I noticed that you are using the Cond formats to also create pyjama stripe 'paper'. Don't forget that the new Tables functionality that was introduced in Excel 2007 allows you to do this by setting 'themes' to ranges of data.

HTH

**squiggler47** · 05-18-2010, 11:13 AM

try

=IF(C2="",FALSE,((TODAY()-C2)>=320))

where the 320 is (365-DaysBefore) or in the case of years (365*Years)

**knowledgeiskey** · 05-21-2010, 05:09 PM

Thank you so much Richard! that worked like a charm! I forgot to post and tell you the other day

. and Robert thank you for your input as well I never had a chance to check if your option worked because Richard took care of it for me.

but thank you for your assistance as well. I hope both of you have a great weekend.

**JosephineAppleby** · 06-08-2011, 03:03 PM

Hello,

I have a training matrix done on Excel and I am having trouble adding expiry dates. I want it to make the box turn yellow when the employees training has 30 days until expiry and then once its expired turn red.

Any help would be grealy appreciated!

Thanks

**Richard Buttrey** · 06-08-2011, 07:39 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.