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





      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





      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.