3 Replies Latest reply on Jul 26, 2016 7:10 AM by sreekanth kancharla

# Calculate Difference in Years, Months and Days between Two Dates

Hi All,

I am new to the Tableau.

I having table in a  database.

Table Name: Workdetails.

 WorkId Start Date End Date 1 1/1/2015 1/2/2015 2 1/1/2015 1/5/2015 3 1/1/2015 2/3/2015 4 1/2/2015 1/10/2015 5 2/10/2015 4/10/2015 6 2/12/2015 3/5/2015 7 3/31/2015 4/1/2015 8 12/30/2015 12/31/2015 9 12/31/2015 1/2/2016 10 12/31/2015 2/10/2016

I am try to generating the report in below

WorkIdStart DateEnd DateDuration

The Duration having the format : years Months Days

As like the below table

 WorkId Start Date End Date Duration 1 1/1/2015 1/2/2015 0  Years  0 Months 1 Days 2 1/1/2015 1/5/2015 0  Years  0 Months 4 Days 3 1/1/2015 2/3/2015 0  Years  1 Months 2 Days 4 1/2/2015 1/10/2015 0  Years  0 Months 8 Days 5 2/10/2015 4/10/2015 0  Years  2 Months 0 Days 6 2/12/2015 3/5/2015 0  Years  0 Months 21 Days 7 3/31/2015 4/1/2015 0  Years  0 Months 1 Days 8 12/30/2015 12/31/2015 0  Years  0 Months 1 Days 9 12/31/2015 1/2/2016 0  Years  0 Months 2 Days 10 12/31/2015 2/10/2016 0  Years  1 Months 10 Days 11 1/1/2015 10/5/2016 1  Years  9 Months 4 Days

How to generate the Tableau report in the above formate .

Thanks

• ###### 1. Re: Calculate Difference in Years, Months and Days between Two Dates

Hi,

Use DateDiff function.The syntax is like

1.DateDiff('year',[Start Date],[End Date])

2.DateDiff('month',[Start Date],[End Date])

3.DateDiff('day',[Start Date],[End Date])

regards,
Venkata Sreekanth

1 of 1 people found this helpful
• ###### 2. Re: Calculate Difference in Years, Months and Days between Two Dates

Hi Sreekanth,

Please find the below attached code, result screenshot and .twbx file (9.3).

Name - Calc_Format

IF (DATEDIFF('day',[Start Date],[End Date]))/30<1 then "0 Year 0 Month "+STR(DATEDIFF('day',[Start Date],[End Date]))+" Days"

ELSEIF (DATEDIFF('day',[Start Date],[End Date]))/30<12 THEN  "0 Year "+STR( ((int(DATEDIFF('day',[Start Date],[End Date])))-(DATEDIFF('day',[Start Date],[End Date])%30))/30)+" Months "+STR(DATEDIFF('day',[Start Date],[End Date])%30)+" Days"

ELSEIF (DATEDIFF('day',[Start Date],[End Date]))/30>12 THEN  STR( INT((((int(DATEDIFF('day',[Start Date],[End Date])))-(DATEDIFF('day',[Start Date],[End Date])%30))/30)/12))+" Year "+STR( (((int(DATEDIFF('day',[Start Date],[End Date])))-(DATEDIFF('day',[Start Date],[End Date])%30))/30)%12)+" Months "+STR(DATEDIFF('day',[Start Date],[End Date])%30)+" Days"

END

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 3. Re: Calculate Difference in Years, Months and Days between Two Dates

Thanks for the Ashish Chaudhari.