I am using Excel 2003. I am trying to determine a child's due date for their
next dental exam but running into headaches and errors. What I am hoping to
have is a formula to calculate the dental exam due date based on the child's
removal date, DOB, and date of their last exam, if any.

a sample of data is:

D F G
I
Child's DOB Date of last exam next exam due by removal date
07/02/2006 04/03/2008 07/01/2010 02/28/2007
05/30/2001 No prior exams 05/29/2005 03/20/2003
09/20/2000 08/01/2008 09/19/2009 07/15/2007
12/12/2005 No prior exams 07/06/2008 06/06/2008
04/05/2006 11/12/2008 04/04/2010 05/15/2007

A child is supposed to have a dental exam within 30 days after their removal
date, and after that time an exam during each age group (age 3, 4, 5, 6 . .
..). Except if the child was already removed from their home before age three
and didn't have an exam, then their exam would be due before their 4th
birthday. If the child was removed from their home before age three and they
had an exam before removal, then the date of that exam would need to be
within their current age time period (before next birthday), otherwise the
child is overdue.

I had this formula, but realize it doesn't work for all possibilities. I
think it would be better to set it up as considering whether the latest exam
occuring during the child's current age time period, but can't figure it out.


=IF(ISBLANK(I3),"",IF(ISTEXT(F3),DATE(YEAR(I3),MON TH(I3)+1,DAY(I3)+1),IF(DATE(YEAR(I3),MONTH(F3),DAY (F3))=DATE(YEAR(I3),MONTH(D3),DAY(D3)),DATE(YEAR( F3)+2,MONTH(D3),DAY(D3)-1),DATE(YEAR(F3)+1,MONTH(D3),DAY(D3)-1))))

Thank you for the help!