Conditional formatting with IF, AND statements to evaluate due dates

Hello, I have a rather complicated training grid I have set up to track everything for my employees and have set up conditional formatting to draw my attention to upcoming due dates/license expiration dates, anniversaries, etc. Most of this I have been able to do with just a little research. The final element that I need is the most complicated as it has to do with continuing education hours for each employee. These are tracked by year, but the year is measured from the employee's birthdate to birthdate (so for example, if my birthday is 2/16/86 then my continuing educations hours are calculated for this year from 2/16/13 - 2/16/14). To make matters worse, there is a different requirements depending on your position- Caregivers have to have 12 hrs of CEUs, non-caregiving staff have to have 6 hrs of CEUs, and RNs/LPNs are exempt because they have to have different CEUs than the rest.

It is complicated so I have attached a sample. I currently have the grid set to flag the cells (AB for 2012 CEUs ad AC for 2013 CEUs) yellow if it is left blank, purple if the person is a caregiver and has under 12 CEUs, purple if the person is NOT a caregiver and has under 6 CEUs, and white if the person is an RN/LPN. What I actually want it to do is read the birthdate in column AD and if the person has under their required amount of CEUs AND they are due within 30 days from today then it will change the background light orange.

An example for employee #6- This person is a Caregiver whose birthday is 8/22/1973 and they will need 12 CEUs by 8/22/13 but they currently have only 7. Because this is due next month, I want the cell to be flagged orange. One the person gets their 12 hours in, I want the cell to have no background color. So basically I want excel to do this:

IF column C (their position) contains the word "Caregiver", AND today's date is less than 30 days from their birthday in the current year, AND the value of the cell is less than 12, THEN the cell is orange.

I know this is confusing, but if anyone understands what I am asking and can help I would greatly appreciate it. Also, if this is impossible, please tell me and I will quit stressing about is and call my current grid good enough. Thanks!

I have tried this in the conditional formatting formula bar:

=IF(C7="Caregiver", AB7<12, DATE(YEAR(2013), MONTH(AD7), DAY(AD7))<TODAY()+30)

and it did not work. It works up until I add in the date stuff, so I know that it is reading the part about being a caregiver and having under 12 hours fine, I am just unsure how to tell it that it also needs to be within 30 days of that person's birthday this year.

Hi FDibbins, Thanks for the help. Your solution is close, but the date is still not quite there. Your formula does flag anything that is for a caregiver, under 12 hours, and on the birthday of the current year. What I would like is for it to flag the cell if today is within 30 days of the birthday for the current year or past the birthday of the current year.

Hallelujah! I think I've got it! Your formula did help me, FDibbins.

Here is the final formula I used in case anyone else is having a similar issue:

=AND(C7="Caregiver",AB7<12,DATE(2013,MONTH(AD7),DAY(AD7))<TODAY()+30)