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

Calculate Difference in Years, Months and Days between Two Dates

sreekanth kancharla Jul 25, 2016 11:36 PM

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

WorkId	Start Date	End Date	Duration

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

venkata sreekanth Jul 26, 2016 12:22 AM (in response to sreekanth kancharla)

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
Like Show 0 Likes(0)

Actions
2. Re: Calculate Difference in Years, Months and Days between Two Dates

Ashish Chaudhari Jul 26, 2016 12:28 AM (in response to sreekanth kancharla)
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

Year Month Day format.twbx 31.4 KB

1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions
3. Re: Calculate Difference in Years, Months and Days between Two Dates

sreekanth kancharla Jul 26, 2016 7:10 AM (in response to Ashish Chaudhari)

Thanks for the Ashish Chaudhari.
Like Show 1 Likes(1)

Actions

Go to original post

Tableau Community Forums

Calculate Difference in Years, Months and Days between Two Dates

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

2. Re: Calculate Difference in Years, Months and Days between Two Dates

3. Re: Calculate Difference in Years, Months and Days between Two Dates

Actions

More Like This