7 Replies Latest reply on Mar 9, 2016 10:11 AM by Tableau kumar

# Converting the results of a DATEDIFF (day) into Years, Months and Days

I'm attempting to convert a value that is derived from the DATEDIFF function into Years, Months and Days.  I'm using the following to derive the number of days:

DATEDIFF('day',[Date Acquired],Today())

Does anyone know how to covert that number into Years, Months and Days?

• ###### 1. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

Hi Jeff, you can always just change the DATEDIFF Calculation to use 'month' or 'year' instead.

• ###### 2. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

There is a handy table in the online help that shows you which date values you can add to Tableau's date functions at the top.

• ###### 3. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

How would you string together 'year','month','day'?

• ###### 4. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

Hi Jeff, I think I'm missing the desired result or what exactly the issue is, do you have a sample workbook that you can provide along with the expected answer?

• ###### 5. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

Nicholas,

I'm trying to show the age of medical equipment based on the Date Acquired.  Currently, I can only show the results as number of days or months or years.  I'd like to show the age by Years, Months and Days combined.  For example, I created a formula in Excel to give me the desired results:

=DATEDIF(\$I2,TODAY(),"y")&" years,"&DATEDIF(\$I2,TODAY(),"ym")&" month(s), "&DATEDIF(\$I2,TODAY(),"md")&" Days"

Oldest piece of equipment I have is 5907 days old.  Converted the result is 16 Years, 2 Months, 3 Days.

Does this make sense?

• ###### 6. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days

Ah, that makes sense now!

ok, it's a bit more complicated than a simple datediff calculation.

Jonathan Drummey wrote a great article for small time durations that introduces a fundamentally similar concept.

See if the article helps, but I do think you may run into trouble with leap years.

• ###### 7. Re: Converting the results of a DATEDIFF (day) into Years, Months and Days
3 of 3 people found this helpful