Month Difference

HCL Domino Domino Forum
1

I am trying to calculate the difference in Months between two date fields in formula. However, if date1 is after the 15th and date two is the 15th or before it doesn’t work. Example date1 = 11/22/2006 and date2 = 3/15/2007 and I want the answer to be 5, I get 4??? It does not matter what day of the month I need to count that month(date1) to the full month of date2. I hope I explained it. date1 and date2 are date/time fields (editable).

Here is my code so far

diff := To - From;

years := ((((diff/60)/60)/24)/365.25);

ryears := @Integer(years);

months :=(years -ryears)*12;

rMonths := @Integer(months);

rMonths

Any suggestions would be appreciated. Thanks!

2

Subject: Month Difference

d1:=Date1FieldName;d2:=Date2FieldName;

@If(d1=“” | d2=“”;@Return(0);“”);

yr1:=@Year(d1);

mo1:=@Month(d1);

yr2:=@Year(d2);

mo2:=@Month(d2);

@If(

yr2<yr1;0;

yr1=yr2 & mo2<mo1;0;

yr1=yr2;mo2-mo1;

mo1=mo2;(yr2-yr1)*12;

mo2<mo1;((yr2-yr1)*12)-(mo1-mo2);

((yr2-yr1)*12) + (mo2-mo1))

3

Subject: RE: Month Difference

Thanks for the suggestion, but it is not working. Example date1 is 11/22/2006 and date2 is 3/15/2007 which means the difference should be 5 months. I am getting 3 as the result? My posting of my code I added +1 and I got the difference, unless date1 is after the 15th of the month. I know dates are tricky so I really appreciate all the suggestions.

Thanks again.

4

Subject: RE: Month Difference

I thought I would publish my solution I came up with in case anyone else would like to know.

Thank you all for your suggestions.

diff := To - From;

years := ((((diff/60)/60)/24)/365.25);

ryears := @Integer(years);

months :=(years -ryears)*12;

rMonths := @Integer(months);

day := @Day(From);

result := rMonths + 1;

result2 := @If(day > 15; rMonths +2;result);

@If(@IsError(years);“”;result2)

5

Subject: Month Difference

Perhaps instead of having just ‘@Integer’ in your calculations you could try rounding the value first like: @Integer(@Round(years)) so that the value is either rounded up or down to the nearest whole number. @Integer will remove any decimal point component.

Alex

6

Subject: RE: Month Difference

Thanks for the suggestiong, I tried it and I still get 4. The answer should be 5 (from November to March)