IF statement together with a couple of functions

**Kagesen** · 04-21-2011, 12:27 AM

Hi to all excel guru's in here :-)

I have a real headache thinking about how I can come up with a genious formula to improve/streamline my excel sheet.

In the attachment you will see my setup. What is important to focus on is column F. The information listed in column F is the date when a specific persons crane certificate expires. For example cell F3 shows that Domingo N General's Hydralift certificate expires on the 18.06.2012.

What I would like to have set up is a system that automatically picks out those certificates that expire within 3 months or have expired. This system will be able to provide me with valuable information because I can better plan refresher training so no certificates expire. In an ideal world the system should assist me in better planning, minimize human errors which in return means more time for other tasks.

I hope there is an extremely talented excel individual out there who can help me with my query. What I have thought of so far is to use the IF statement together with LOOKUP but I just dont know how to do the DATE thing.

Thank you in advance.

**arthurbr** · 04-21-2011, 02:10 AM

Hi

why post an image when you need a spreadsheet?

**Kagesen** · 04-21-2011, 02:28 AM

You are totally correct arthurbr - I have now attached a copy of my excel sheet. Please assist me if possible and thank you for spotting my error.

**Cutter** · 04-21-2011, 07:54 AM

I have applied Conditional Formatting to columns F and G.
When the date has arrived or passed the cell turns red and when the date is withing 3 months of today's date the cell turns yellow.

The CF formulas are:
=AND(F3<>"",TODAY()>=F3)
and
=AND(F3<>"",TODAY()>=EDATE(F3,-3))

I had to change your date formats to one that my comp recognizes so change them back on yours.

**Kagesen** · 04-24-2011, 02:24 AM

Hi Cutter.

This looks great. You are brilliant!

I have not worked with that Conditional Formatting before but I want to try and do what you have written above. I knew there was a very bright head in here somewhere - THANKS Cutter.