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

# 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

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

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

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:

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;

Commitment Cal Day Dt

01/25/2015

04/20/2013

05/25/2015

12/20/2015

];