4 Replies Latest reply on Sep 6, 2018 7:03 AM by Imran Azam

# Length of contract between start and end date  calculation field help

Hi Guys

i am created a report which has a start of contract date and end of contract date and i need to find out the length of contract in months.

the rule i want to set is this

If the end date month and year  = start date month year than length should be 1

if the date difference between  start date   and end date is <1 then length should be 1

IF the DAY of end date is 15 days more than the DAY of start date then count this as an extra month

else number of months between start date and end date.

i have attached a a tableau workbook with three date my current formula ( length of contract) does not do the above, on the workbook below is what it show be

start date : 25/06/2017  enddate: 07/07/2018 should show 11 months mine is showing 12

start date : 02/03/2018  enddate: 05/03/2018 should show 1 months mine is showing 0

start date : 02/03/2018  enddate: 05/03/2018 should show 2 months mine is showing 2 ( this one is coming out correct

on excel i created the below formula which worked for what i want

=IF(Enddate=startdate,IF(DATEDIF(startdate,enddate,"m")<1,1,DATEDIF(startdate,enddate+15,"m")))

but i don't know how i can create this into tableau

can anyone help?

thank you

 Start Date End Date 25/06/2018 07/06/2019
• ###### 1. Re: Length of contract between start and end date  calculation field help

Hi Imran,

you have to multiply the (DATEDIFF('day',[Start Date],[End Date])) with 0.0329 to get length of contract in decimals.

Once you get the length in decimal you can use below formula.

IF [Calculation1] <= 0.5 Then CEILING([Calculation1]) ELSEIF [Calculation1] >= 0.5 THEN FLOOR([Calculation1]) END

hope the above solution will return you desired output .

For more details please find attached twbx

Regards,

Anupam

1 of 1 people found this helpful
• ###### 2. Re: Length of contract between start and end date  calculation field help

Hi Imran,

You can still get the correct result by directly translating your logic into formula.

IF DATEDIFF('month', [Start Date], [End Date]) < 1 THEN 1

ELSE

IF DAY([Start Date]) - DAY([End Date]) <= 15 THEN

DATEDIFF('month', [Start Date], [End Date])

ELSE DATEDIFF('month', [Start Date], [End Date]) - 1

END

END

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 3. Re: Length of contract between start and end date  calculation field help

thank i will try this

1 of 1 people found this helpful
• ###### 4. Re: Length of contract between start and end date  calculation field help

Thank you guys for your help