4 Replies Latest reply: Jan 15, 2016 4:21 PM by Sunny Talwar RSS

    Subtracting one Date from Another

      Hello,


      I am trying to determine the months elapsed from a field [Commitment Cal Day Dt] and 12/31/14.  The format of the field 'Commitment Cal Day Dt' is D/M/YYYY or DD/MM/YYYY depending on if the day and date have 2 digits.  I tried using the following line of code in my script. 

       

      year(MakeDate(2014,12,31))*12 +month(MakeDate(2014,12,31)) - (Makedate([Commitment Cal Day Dt],01)) as [Months Elapsed],

       

      It gives me a large negative number.  Any thoughts?

       

      Thank you!

        • Re: Subtracting one Date from Another
          Sunny Talwar

          You are using MakeDate() function. Is [Commitment Cal Day Dt] a year field or date? If it is already date you don't need to use MakeDate.

            • Re: Subtracting one Date from Another

              It is already a date.

               

              Would it simply be as follows?

               

              year(MakeDate(2014,12,31))*12 +month(MakeDate(2014,12,31)) - [Commitment Cal Day Dt],01 as [Months Elapsed],

               

              I still get a large negative number.  I appreciate any tips!

                • Re: Subtracting one Date from Another
                  Sunny Talwar

                  Month calculation tend to be a difficult one. Try this may be, I have used in one of my applications. For me the exact number of months was not very important so I think in particular cases it calculates a extra month. But you can fine tune this for your requirements.

                   

                  If(MakeDate(2014,12,31) > [Commitment Cal Day Dt], 0,

                       If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

                               Month([Commitment Cal Day Dt]) - Month(MakeDate(2014,12,31)),

                               12 - Month(MakeDate(2014,12,31) ) +  Month([Commitment Cal Day Dt])

                       )

                  +

                       If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

                            ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ))*12),

                            ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ) - 1)*12)

                       )

                  )

                   

                  Attaching a sample:

                  Capture.PNG

                   

                  Script used:

                   

                  Table:

                  LOAD *,

                    If(MakeDate(2014,12,31) > [Commitment Cal Day Dt], 0,

                       If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

                               Month([Commitment Cal Day Dt]) - Month(MakeDate(2014,12,31)),

                               12 - Month(MakeDate(2014,12,31) ) +  Month([Commitment Cal Day Dt])

                       )

                  +

                       If(Month([Commitment Cal Day Dt]) > Month(MakeDate(2014,12,31)),

                            ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ))*12),

                            ((Year([Commitment Cal Day Dt]) - Year(MakeDate(2014,12,31) ) - 1)*12)

                       )

                  ) as Month_Elapsed;

                  LOAD * Inline [

                  Commitment Cal Day Dt

                  01/25/2015

                  04/20/2013

                  05/25/2015

                  12/20/2015

                  ];