+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    Question 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.
    Last edited by sharpmel; 12-05-2012 at 03:16 PM.

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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
    Gary's Student

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #4
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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. #6
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #7
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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. #8
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #9
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

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

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

  10. #10
    Registered User
    Join Date
    11-27-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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"))
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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