1 2 Previous Next 17 Replies Latest reply on Sep 14, 2018 12:34 PM by Shinichiro Murakami

# length of contract in month help

Hi ,

I have a start date and an end date, i have done a calculation filed to work out the number of months between the two dates based on some rules, the calculation generally seems to be working but for two dates ( shown in attachment ) it is bi=ring back 13 months when it should be 12.

Can anyone please check this and tell me what i have done wrong?

I have attached the workbook .

Thank you

• ###### 1. Re: length of contract in month help

Hi, Imran

I remembered this is something I provided previously, but I don't understand why 13 is incorrect. it is more than 1 year, so the length is 13. ZZ

• ###### 2. Re: length of contract in month help

HI Thanks for the previous help and the reply

it is adding up to 12 months and 8 days that should bring back 12 months as its less than 15 days into the month,

• ###### 3. Re: length of contract in month help

Hi, Imran

try below fix IF DATEDIFF("month",[Start Date],[End Date])<1 THEN 1

ELSEIF DATEDIFF("month",[Start Date],[End Date])>=1 AND

DATEDIFF("day",

DATEADD("month",FLOOR(DATEDIFF("month",[Start Date],[End Date])),[Start Date]),

[End Date]

)

<=15

AND

DATEDIFF("day",

DATEADD("month",FLOOR(DATEDIFF("month",[Start Date],[End Date])),[Start Date]),

[End Date]

)

> 0

THEN

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

ELSEIF

DATEDIFF('day',[Start Date],[End Date]) > 365 AND DATEDIFF('day',[Start Date],[End Date]) <380 THEN

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

ELSE

DATEDIFF("month",[Start Date],[End Date])+1

END

ZZ

1 of 1 people found this helpful
• ###### 4. Re: length of contract in month help

Hi Zhouyi,

Thank you for your help

this kind of fixes this but then what it does is counts another combo to 13 when it should be 12 as shown below and in the attachment, the previous formula counted this as 12 • ###### 5. Re: length of contract in month help

can anyone help?

• ###### 6. Re: length of contract in month help

Could you explain the expected condition logic one more time here?

Based on that , the formula might be simplified.

Thanks

Shin

• ###### 7. Re: length of contract in month help

Hi Shinchiro,

if you open the second attachment you will understand what i am trying to do

i am need the the count of the months to be 12 instead they coming to 13

• ###### 8. Re: length of contract in month help

I suspect this formula logic is correct or not.

To know that, Could you explain by TEXT what logic you are trying to apply.. especially for the judging point of "15".

Thanks,

Shin

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

ELSEIF DATEDIFF("month",[Start Date],[End Date])>=1 AND

DATEDIFF("day",

DATEADD("month",FLOOR(DATEDIFF("month",[Start Date],[End Date])),[Start Date]),

[End Date]

)

<=15

AND

DATEDIFF("day",

DATEADD("month",FLOOR(DATEDIFF("month",[Start Date],[End Date])),[Start Date]),

[End Date]

)

> 0

THEN

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

ELSEIF

DATEDIFF('day',[Start Date],[End Date]) > 365 AND DATEDIFF('day',[Start Date],[End Date]) <380 THEN

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

ELSE

DATEDIFF("month",[Start Date],[End Date])+1

END

• ###### 10. Re: length of contract in month help

Thank you for the rely

this is giving me a head ache now been trying to fix this for two days!!

regarding the 15 what i am trying to say is this the end date is 15  or less days away from the previous month then count that as 1 month and not 2

e.g

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

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

in a nutshell i have an excel formula which i am trying to change into tableau

• ###### 11. Re: length of contract in month help

The answer might be dis-ordered, but above logic does not work ?

If you provide me with excel file, I can check.

Thanks,

Shin

• ###### 12. Re: length of contract in month help

ok

IF(end date=start date,1,IF(DATEDIF(start date,end date,"m")<1,1,DATEDIF(\$start date,end+15,"m")))

that is the formula on excel,  you can test this on the second workbook i attached the length on all 4 records should be 12.

• ###### 13. Re: length of contract in month help

It's already 12 with my previous attachment.

But need more test.

I am asking below simplified logic brings same result in your excel or not. Thanks,

Shin

• ###### 14. Re: length of contract in month help

yes it does

1 2 Previous Next