12 Replies Latest reply on Nov 4, 2013 7:02 AM by Alex Kerin

Date Calculations (years) wrong in Tableau

Hi,

I found this consistently on Tableau, calculating the age using DATEDIFF function is flawed!

Here's is my example:

This is my calculation:

datediff('year',[DOB],#July 25, 2013#)

DOB field is the format - dd/mm/yyyy

So for a DOB of -

07/10/1943

The correct Age should be - 69 years but Tableau reports as 70.

I diagnosed a bit and found that - Tableau does indeed reads the date in correct format - so I ruled it out.

Here's a screenshot of that record

The age should be reported as 69, not 70. Can a Tableau colleague help or explain the issue here?

Urgent help is much appreciated!

Thanks

Sheron

• 1. Re: Date Calculations (years) wrong in Tableau

Sheron, you say your [DOB] date is dd/mm/yyyy and yet you are giving a mm/dd/yyyy (#July 25, 2013#) date to Tableau in your calculation. Try:

datediff('year',[DOB],#25/07/2013#)

--Shawn

• 2. Re: Date Calculations (years) wrong in Tableau

Hi Shawn,

I just tried

Here's my changed calculation:

datediff('year',[DOB],#25/07/2013#)

Still get the age as 70

• 3. Re: Date Calculations (years) wrong in Tableau

Is your data coming from Excel?

• 4. Re: Date Calculations (years) wrong in Tableau

No from a text file. Tab Delimited.

I have run an extract out of this to see whether that makes a difference. No difference.

thanks

• 5. Re: Date Calculations (years) wrong in Tableau

Hi Sheron,

Calculating years with DATEDIFF is no different then YEAR(date2) minus YEAR(date1).

Even 1 day equals 1 year for this formula:   DATEDIFF('year', #12/31/2012#, #1/1/2013#).

This formula works:

datediff('year',[Data_Date],[My_Date]) +

if RIGHT('0' + STR(DATEPART('month', [Data_Date])), 2) + RIGHT('0' + STR(DATEPART('day', [Data_Date])), 2) >

RIGHT('0' + STR(DATEPART('month', [My_Date])), 2) +  RIGHT('0' + STR(DATEPART('day', [My_Date])), 2) then -1

else 0

end

See more in attached workbook.

Johan

1 of 1 people found this helpful
• 6. Re: Date Calculations (years) wrong in Tableau

I found a simpler and faster formula in  Calculate age from date of birth

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

ELSE

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

END

Ps. I found this formula using following search:  http://community.tableau.com/search?q=datediff+age

1 of 1 people found this helpful
• 7. Re: Date Calculations (years) wrong in Tableau

Sheron, I think you've actually discovered a bug. When you brought your txt file in the DOB probably came in as a string type and looked something like this:

But then you probably converted the string to a date. Tableau then produces this:

Tableau is treating the first one as a mm/dd/yyyy date, but since there isn't a month 13, Tableau converts the second one so it 'makes since' as a date. Essentially it is treating the two dates differently (!!). And that's why I consider this a bug, (or at least Tableau being far too 'helpful'.)

Now when you go in and change the formatting for the DOB field to dd/mm/yyyy, Tableau dutifully does what you asked and changes the date to this:

Simply reformatting both dates, but the error is already baked in, so now the first date is going to be wrong.

I haven't tested it it, but I suspect the workaround is to open the text file in Excel, format the dates as mm/dd/yyyy, then connect to the Excel file and change the date format for DOB to dd/mm/yyyy. I'm suggesting leaving the Excel as the mm/dd/yyyy format because I'm not sure how Tableau is going to 'see' the dates when you connect to them. So best to leave the formatting change to Tableau.

Hope this helps,

--Shawn

EDIT: Again this is untested, but I suspect that if you changed your computer locale to the UK and then connected to the text file, you wouldn't have any problems. When you converted the DOB from string to date, Tableau would read it as dd/mm/yyyy

2 of 2 people found this helpful
• 8. Re: Date Calculations (years) wrong in Tableau

Similar:

datediff('year',#6/1/1970#,[Order Date])+

if ([Order Date]-datetrunc('year',[Order Date]))>=#6/1/1970#-datetrunc('year',#6/1/1970#) then 1 else 0 end

1 of 1 people found this helpful
• 9. Re: Date Calculations (years) wrong in Tableau

Thanks for the help!

Really appreciate it.

However I think Tableau should be able to fix this issue.

I have done the same using MS-ACCESS(my colleague) and SQL server, we got the correct results with no additional calculations or formatting change!

Sheron

• 10. Re: Date Calculations (years) wrong in Tableau

Thanks for the help and the formulae works!

Really appreciate it.

However I think Tableau should be able to fix this issue.

I have done the same using MS-ACCESS(my colleague) and SQL server, we got the correct results with no additional calculations or formatting change!

Sheron

• 11. Re: Re: Date Calculations (years) wrong in Tableau

I wonder what function you use in SQL Server?

To my my knowledge SQL Server and Tableau use DATEDIFF in the same way.

This formula returns 1 (year) in SQL Server 2012 even if the difference is only 1 day:

```select datediff(year, '2012-12-31', '2013-01-01') as age

```

Ps. It is important to choose one of the answers as correct (if any is), because it closes this question.

Knowing that a question is answered saves helpers' time.

It also saves Tableau staff  the time to mark the question as Assumed Answered.

In addition it makes helpers happy seeing their answers stamped as correct.

• 12. Re: Date Calculations (years) wrong in Tableau

To be fair, it's not a bug - that is the way the calculation is designed to work