
Re: How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?
Jonathan Dienst May 11, 2017 12:53 AM (in response to Gaston Tan )1. No MonthDiff function. Try this:
=12 * (Year(date1)  Year(date2)) + Month(date1)  Month(date2)
(insert the actual date expressions in place of date1/date2.
2. Try
=Count(If(12 * (Year(date1)  Year(date2)) + Month(date1)  Month(date2) > 3, <expression>))
(insert correct date expression and the expression that you are trying to count)

Re: How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?
Gaston Tan May 11, 2017 2:24 AM (in response to Jonathan Dienst )Hi Jonathan,
Thanks for your help. However i am unable to use the equation as it doesn't work.
12 * (Year(UTC(now()))  Year([DateOfCreation]) + Month(UTC(now()))  Month([DateOfCreation])
Doesn't this equation pose a problem. For example 01032017  28022017 would produce the result as one month. However in fact, only one day has passed. I want to include the days in counting the month difference. If there is no way, i can just count the days difference and divide it by 30.
I am unable to get the 2nd part right too.
Count(if(12 * (Year(UTC(now()))  Year([DateOfCreation]) + Month(UTC(now()))  Month([DateOfCreation]) > 3, <expression> ))
May i have an example of <expression>.
Huge thanks,
Gaston

Re: How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?
Jonathan Dienst May 11, 2017 3:17 AM (in response to Gaston Tan )>>12 * (Year(UTC(now()))  Year([DateOfCreation]) + Month(UTC(now()))  Month([DateOfCreation])
Missing a parenthesis:
=12 * (Year(UTC(now()))  Year([DateOfCreation])) + Month(UTC(now()))  Month([DateOfCreation])
What values do Year([DateOfCreation]) and Month([DateOfCreation]) give?
Is DateOfCreation a proper numeric date field?Or is it a number in the form ddmmyyyy?
If the latter:
=12 * (Year(UTC(now()))  Year(Date#(DateOfCreation, 'ddMMyyyy')) + Month(UTC(now()))  Month(Date#(DateOfCreation, 'ddMMyyyy'))
If you convert the field to a proper date value on initial load, you wont need all the date mangling in the previous expression.
You could also say:
=12 * (Year(UTC(now()))  Right(DateOfCreation, 4)) + Month(UTC(now()))  Mid(DateOfCreation, 4, 2)
>>May i have an example of <expression>.
<expression> is just a placeholder for whatever you are trying to count. Probably a field name.

Re: How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?
Gaston Tan May 11, 2017 3:46 AM (in response to Jonathan Dienst )Hi Jonathan,
Thanks for your suggestion. I understand the convenience of converting the field to a proper date value during the initial load. Given my environment i am unable to do so, this is because i am not using the data manger to add it but rather the data load editor to load the table from a script.
That aside, i have formulated the code you provided which allows me to count the months.
12 * (year(now())  year(date#(DateOfCreation,'DDMMYYYY'))) + month(now()) month(date#(DateOfCreation,'DDMMYYYY'))
This poses the same problem as mentioned earlier. Comparing dates between 01032017 and 28022017 gives me the difference as 1 month, when in fact it should be 0 as only one day has passed. What i would like is to be able to compare the entire date field.
For example,
28032017  28022017 = 1 month
05042017  28022017 = 1 month
28042017  29022017 = 2 months
31012017  28022017 = 1 month (because its the last day of Feb)
I'm not sure if you catch my drift, but if there is no function or expression to calculate this, i guess i'll just go with the assumption the every month has 30 days.
Regards,
Gaston

Re: How to calculate date difference in terms of months? And count number of application exceeding a certain lifespan?
Gaston Tan May 11, 2017 3:53 AM (in response to Gaston Tan )I have gotten the answer for your second part too.
Thanks.



