8 Replies Latest reply on Sep 7, 2018 4:04 PM by Hari Ankem

# contract length problem

Hi guys

i have 3 starts dates and 3 end dates and i need to figure out the length of contract by months ( based on a few rules).i have created a calculation field but for some reason i am not getting the correct numbers.

the calculation fields i am using is the below

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

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

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

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

END

this doesn't always get the correct difference

for start date : 01/03/2018 and end date: 30/04/2018 i am getting 1 when it should be 2, for start date 01/06/2018 and end date 30/11/2016 i am getting 5 when it should be 6. the third one is correct.

It should look like the below

Start Date

End Date

length

01/03/201830/04/20182
25/05/201825/05/20181
01/06/201830/11/20166

why is this happening? how can i get the correct length as shown in the above table.

I have attached the work book

thank you for any help.

• ###### 1. Re: contract length problem

Please correct the formula this way:

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

ELSEIF DATEDIFF("day",[Start Date],[End Date])<=15 THEN

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

ELSE

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

END

Hope this helps.

• ###### 2. Re: contract length problem

Hi i tried this it works for the dates i provided but when i add another date which is start date: 11/04/2018 and end date: 11/05/2018 it shows 2 as contract length when it should be 1.

see attachment

• ###### 3. Re: contract length problem

I don't see that problem.

• ###### 4. Re: contract length problem

OK. I guess you had the dates in the months of April and May. So, here is the corrected formula now:

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

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

DATEDIFF("day",

[End Date]

)<=15 THEN

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

ELSE

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

END

1 of 1 people found this helpful
• ###### 5. Re: contract length problem

that's strange, do not understand why i am

did you see the problem with my attachment?

• ###### 6. Re: contract length problem

Did you see the updated formula above?