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

Sign In or
Create your Account

Actions

More Like This

Sign In or Create your Account

Sign In or
Create your Account