Please help with formula (multiple ifs to determine due date)

[tryinghard]

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),MONTH(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!

 

[tryinghard]

I realized that my sample got messed up. I am not sure how to fix but the
columns are supposed to be D: Child's DOB, F: Date of last exam, G, Next exam
due by, and I: removal date. I will try separating the columns, below.

D F

Child's DOB Date of last exam
07/02/2006 04/03/2008
05/30/2001 No prior exams
09/20/2000 08/01/2008
12/12/2005 No prior exams
04/05/2006 11/12/2008

G I
next exam due by removal date
07/01/2010 02/28/2007
05/29/2005 03/20/2003
09/19/2009 07/15/2007
07/06/2008 06/06/2008
04/04/2010 05/15/2007



Please help!

 

[JLatham]

I'd like to try to help you, but I'm getting fuzzy on the rules, so if we can
clarify those then we can probably come up with a solution. I see we have an
initial dividing line of age 3. So we have 2 sets of rules to deal with,
those dealing with pre-3 years old and those dealing with those 3 and older.

So we line those up
Removed before age 3:
no prior exam --
has had prior exam --
Removed after age 3
no prior exam --
has had prior exam --
But we have a special rule: supposed to have an exam within 30 days of
removal (and then 1 during each age group in the out years).

So if you can come up with the list of rules, in a priority to apply them, I
can help with the formula.

If you prefer (and it may be easier to work things out), you can contact me
via email at (remove spaces)
HelpFrom @ JLatham Site.com
and we can exchange notes more easily and eventually post the solution back
here for others to have access to it if they need similar help. Make sure
you reference this post, preferably by URL, or repeat its contents in your
email.

 

[JLatham]

Here's what I have for a row 2 formula that seems to mostly work:

=IF(AND(NOW()-I2<31,F2=""),I2+29,IF((I2-D2)/365<=3,IF(F2="",DATE(YEAR(D2)+4,MONTH(D2),DAY(D2)-1),IF(DATE(YEAR(NOW()),MONTH(D2),DAY(D2))<NOW(),DATE(YEAR(NOW())+1,MONTH(D2),DAY(D2)-1),DATE(YEAR(NOW()),MONTH(D2),DAY(D2)-1))),IF(DATE(YEAR(NOW()),MONTH(D2),DAY(D2))<NOW(),DATE(YEAR(NOW())+1,MONTH(D2),DAY(D2)-1),DATE(YEAR(NOW()),MONTH(D2),DAY(D2)-1))))

Where it doesn't work is for the child on row 5: DOB 12/12/2005, no prior
exam, removed on 6/6/2008 (under 3 yrs old).
My formula shows due date of 12/11/2009 (before 4th birthday), but your's
shows 7/6/2008 (w/in 30 days of removal, but that date has already passed us
by).

So I need some help in deciding what to do in that circumstance. All other
dates returned by my formula are exactly the same as in your example data.