6 Replies Latest reply on Oct 11, 2016 1:36 AM by Richard van Dijk

# Calculate age from date of birth

I'm trying to calculate the age of a person returned in a search from their date of birth by creating a calcuated field and have run into some trouble.

If i create a calculated field titled "Age" with the forumula

DATEDIFF('day', [BirthDate], TODAY()) / 365

I get close, but obviously due to leap years this goes slightly wrong.

What I'm ideally trying to do is replicate the C# code I've written below

DateTime now = DateTime.Today;

int age = now.Year - DateOfBirth.Year;

if (DateOfBirth.Value.Date > now.AddYears(-age)) age--;

Return age;

I'm having trouble doing this in the calculated field dialog. If I do the below:

Create two parameters, year and day

write the code

Year = DATEDIFF('year', [BirthDate], TODAY())

Day = DATEDIFF('day', [BirthDate], TODAY())

I get a syntax error on my second line. I've searched but can't appear to find any requirement for an endline character. Clearly I'm missing something here but I'm puzzled as to what. Any help would be apreciated.

• ###### 1. Re: Calculate age from DOB

What is your full calculation? You need an end after an if statement.

• ###### 2. Re: Calculate age from DOB

Hi Alex,

Thanks for your response. I believe you're referring to the C# code I posted? (I'm aware this won't work in a tableau calculated field but I'm simply using it to illustrate what I'm trying to achieve)

If you are referring to the C# code then what I have typed above is syntactically valid, it might be a little clearer expressed as follows however:

if (DateOfBirth.Value.Date > now.AddYears(-age))

{

age--;

}

I'm trying to perform the same calculation in Tableau as I have in the C# code. (Sorry if that isn't very helpful but C# is my preferred language and I found it the most concise way to express my intended calculation)

My intended full calculation will take a date of birth and return the persons age in years

• ###### 3. Re: Calculate age from DOB

HI Tom,

Will something like the below work?  It appears to work on my side in Tableau, but I didn't test specifically for a leap year case.  This would be for the Age calculation to reproduce, at least logically, what you have in c# code above.

DATEDIFF('year',[DOB],TODAY())-1

ELSE

DATEDIFF('year',[DOB],TODAY())

END

Regards,

Marc

3 of 3 people found this helpful
• ###### 4. Re: Calculate age from DOB

Hi Marc,

Thanks very much. This has worked perfectly! You've saved me a lot of head scratching!

Kind regards

Tom

• ###### 5. Re: Calculate age from date of birth

What would need to be done to calculate age based on a fixed day? Suppose one is doing historical reporting, using TODAY() would not be accurate.

Can a mm/dd/yyyy be substituted for TODAY ()?

• ###### 6. Re: Calculate age from date of birth

To get Age based on a given Reporting Date use the following:

IF [DoB] > DATEADD('year',-DATEDIFF('year',[DoB],[Reporting Date]),[Reporting Date]) THEN

DATEDIFF('year',[DoB],[Reporting Date])-1

ELSE

DATEDIFF('year',[DoB],[Reporting Date])

END

In your case [Reporting Date] could be a calculated field with DateParse, e.g. DATEPARSE("mm/dd/yyyy","10/04/2016"), or parameter driven using MAKEDATE([Year],[Month],[Day]).

1 of 1 people found this helpful