Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Did you look at Calculating Months difference between two dates
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!
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
];