Subtracting one Date from Another

[Anonymous]

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!

[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.

[nagaiank]

Did you look at Calculating Months difference between two dates

[Anonymous]

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!

[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:

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

];