3 Replies Latest reply on Feb 13, 2017 7:47 PM by sreevidya.yandra

    Age calculation for year, month, day

    jmt6312

      I have Date of Birth and need to convert it into age in YY,MM,DD format. I have tried DATEDIF ('day', [DateOfBirth], TODAY() ) and formatted as a custom number yy,mm,dd, but it doesn't calculate correctly. It appears to be using a standard 30 days per month instead of breaking it down literally. Here's a sample output: (Today's date is 5 March 2013)

       

      agecalc.jpg

      Some calculate correctly. Most do not. The last row is a good example. The output should be 73 years 11 months 5 days.

       

      I've also tried calculating separate DateDifs for year, month and day and combining, but they do not calculate correctly either.

        • 1. Re: Age calculation for year, month, day
          Catherine Rivier

          Hi,

          I believe you're right about how the DATEDIFF calculates.  I know in previous questions about calculating a straightforward age based on a birth date (like here: http://community.tableau.com/message/177324#177324), it is done through a series of IF/THEN statements.

           

          So that's the way I tackled your issue.  I came up with a calculation for years old, months old, and days old based on possible scenarios.  See the attached workbook.  (In the original file, I used the dates you have above, plus a variety of different scenarios to test my formulas.  The data source has the pre-calculated ages I was looking for, so I could check my work.)

          I created "YEARS OLD CALCULATION", "MONTHS OLD CALCULATION", and "DAYS OLD CALCULATION" with the detailed IF/THEN statements figured out.  Then I combine them all into field "Age - FINAL" which uses the format you use above.  Here is my reasoning per scenario, with my logic in red and the translated IF/THEN statement below each one:

           

          YEARS OLD:

          if (todaymonth > bdaymonth) THEN (todayyear - bdayyear)

             IF MONTH(Today()) > MONTH([BirthDate])        THEN DATEDIFF('year',[BirthDate],TODAY())

          else if (todaymonth < bdaymonth) THEN ([todayyear - 1] - bdayyear)

             ELSEIF MONTH(Today()) < MONTH([BirthDate])        THEN DATEDIFF('year',[BirthDate],TODAY())-1

          else if (todaymonth = bdaymonth AND todayday > bdayday) THEN (todayyear - bdayyear)

             ELSEIF (MONTH(Today()) = MONTH([BirthDate])) AND (DAY(Today()) > DAY([BirthDate]))        THEN DATEDIFF('year',[BirthDate],TODAY())

          else if (todaymonth = bdaymonth AND todayday <= bdayday) THEN ([todayyear - 1] - bdayyear)

             ELSE DATEDIFF('year',[BirthDate],TODAY())-1

          END

           

          MONTHS OLD:

          ((todayday >= bdayday) AND (todaymonth < bdaymonth)) THEN (12 + todaymonth - bdaymonth)

             IF (DAY(Today()) >= DAY([BirthDate])) AND (MONTH(Today()) < MONTH([BirthDate]))        THEN DATEDIFF('month',[BirthDate],TODAY())+12

          ((todayday >= bdayday) AND (todaymonth >= bdaymonth)) THEN (todaymonth - bdaymonth)

             ELSEIF (DAY(Today()) >= DAY([BirthDate])) AND (MONTH(Today()) >= MONTH([BirthDate]))        THEN DATEDIFF('month',[BirthDate],TODAY())

          ((todayday < bdayday) AND (todaymonth <= bdaymonth)) THEN (12 + (todaymonth-1) - bdaymonth)

             ELSEIF  (DAY(Today()) < DAY([BirthDate])) AND (MONTH(Today()) <= MONTH([BirthDate]))        THEN MONTH(TODAY())-1-MONTH([BirthDate])+12

          ((todayday < bdayday) AND (todaymonth > bdaymonth)) THEN ((todaymonth-1) - bdaymonth)

             ELSE MONTH(TODAY())-1-MONTH([BirthDate])

          END

           

          DAYS OLD:

          (todayday >= bdayday) THEN (todayday - bdayday)

             IF (DAY(Today()) >= DAY([BirthDate]))

                  THEN DAY(TODAY())-DAY([BirthDate])

          (todayday < bdayday) THEN (DaysinPreviousMonthtoToday) + (todayday - bdayday)

             ELSE DAY(TODAY())-DAY([BirthDate])+[DaysInPreviousMonth]

           

           

          As a side note, I ran into a lot of confusion figuring out how to calculate the number of days old someone is beyond a month.  There are some liberties you can take.  For example, if someone was born on 3/31/1972, then on March 6th 2013, would they be 40 yrs 11 month 6 days old? Or would they be 40 years 11 month 3 days old?   I'm using the former, since the latter will cause issues in how the data looks day to day.  But I can see an argument for the latter....

           

          But other than that, hope this solves your problem, and let me know if any other issues come up with this solution!

          Catherine

          2 of 2 people found this helpful
          • 2. Re: Age calculation for year, month, day
            jmt6312

            Catherine,

             

            Thank you! I started playing with the same logic last night, but was hoping there was an easier route. I agree on the "days old" calculation. Given my audience I am going with your first option too.

             

            Jim

            • 3. Re: Age calculation for year, month, day
              sreevidya.yandra

              Hi,

               

              I have been playing with the year field, but it seems to be giving different values all together.

               

              Could you please let me know where I have gone wrong!

              The year_age is what i have written and the year_1 is from catherine's solution.