Conditional format for overdue date

Hello,

I have tried to firgure this out by reading other threads but cannot seem to find a formula to apply to my spreadsheet. Long story short, I am trying to create a spreadsheet to determine if a medical or dental appointment for a child is over due. Essential I would like the overdue date to be highlighted in red.

So for example, I have the "last medical date" in column "I". Each child is supposed to have a medical every year. In column "L" I have put a formula that inputs the date exactly a year from the "last medical date". I need for the date in column "L" to be highlighted if it is past a year from the date in column "I". I guess it would be helpful too, if I can create another condition that changes the color for dates at 9 months??????

Oh, I am using Excel 2010....

Please help!

Originally Posted by monstur1

I need for the date in column "L" to be highlighted if it is past a year from the date in column "I".

I assumed that you meant, "I need for the date in column "L" to be highlighted if today's date is past a year from the date in column "I". "

Here is an example set up using conditional formatting in Excel 2007 but it should translate to 2010. Let me know if you need an explanation. If it is more than a year since the last medical date, then the date in L is red. If it is less than a year but more than 9 months, it's yellow.

I am STILL working on this spreadsheet to track medical and dental appointment dates and am looking for a conditional format formula to change the color of a cell if a child is under 4 years old. So far, I have Date of birth in column "D" and a formula to calculate the age in column "F". What I am looking for is a formula to change the cell in column J green if the child's age is under 4 years old as a dentist appointment is not needed for children at this age.

All help is appreciated,

Thanks