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

Age calculation for year, month, day

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) 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

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

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

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.