# Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

1. ## Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

I am calculating the age of a child based on their DOB. I currently have =INT((TODAY()-'Qualtrics Output'!N3)/365.25) but I do not believe this accounts for leap years (our study is over 5+ years) and it also occurred to me that this formula will continually update. Basically, I need the child's age based on the day that the information is entered into Excel. To make things even more complicated, I need to calculate the age in months ONLY IF the child is less than 2 years old.

2. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

See:

http://www.cpearson.com/Excel/datedif.aspx

3. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

I have already been to that site. I am having problems connecting it all. As far as I can tell there is no mention of an exclusion (only stating months if below a certain threshold).

4. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

I currently have: =IF(DATEDIF('Qualtrics Output'!I3,'Qualtrics Output'!Q3,"Y")<2,"",DATEDIF('Qualtrics Output'!I3,'Qualtrics Output'!Q3,"M"))

I3= Date of Birth
Q3 = Static date (date they filled out the survey)

5. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

=IF(DATE(YEAR(I3)+2,MONTH(I3),DAY(I3))-Q3>0,DATEDIF(I3,Q3,"m"),DATEDIF(I3,Q3,"y"))

so if i3 has 7/12/2012 and q3 has 8/15/2012 we get 1 for 1 month

if i3 as 7/12/2000 and q3 has 8/15/2012 we get 12 for 12 years

6. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

It seems like the calculation is correct but it does not say what the units are. It correctly calculated 10 (for 10 months old) but there's no way to know what units it is in unless you verify the referenced cells.

7. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

O.K.......................

=IF(DATE(YEAR(I3)+2,MONTH(I3),DAY(I3))-Q3>0,DATEDIF(I3,Q3,"m") & " m ",DATEDIF(I3,Q3,"y") & " y ")

8. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

Yeaaahhhh! Thanks!

...annoying question....sorry in advance...does this account for leap years...?

9. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

yes.............

10. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

How do I add a calculation for weeks old if the kid is less than a month...and days old if less than 1 week?

11. ## Re: Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

Try this version

=IF(Q3-I3<7,Q3-I3&" days",CHOOSE(MATCH(DATEDIF(I3,Q3,"m"),{0,1,2,24}),INT((Q3-I3)/7)&" weeks","1 month",DATEDIF(I3,Q3,"m")&" months",DATEDIF(I3,Q3,"y")&" years"))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1