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

    contract length problem

    Imran Azam

      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.