I'm a part time Crystal user so please excuse the ignorance. I need to use a formula to tell me the number of months between 2 dates. DateDiff does not work as it will return "the count of interval boundaries crossed between two dates”. So for 6th March to 2nd May Datediff gives a return of 2 because 31st March and 30th April are the interval boundaries, but there are not 2 complete months between 6th March and 2nd May.

Can anyone code me a formula so that I can get the correct result?

Many thanks
brian@assyst-software.co.uk

I think you may be overcomplicating this.

Re your example of Feb 28 to March 28 - they are 1 month apart. I think 99% of the population would agree with that.

"30 day buckets" does not work as March 1st and March 31st would not (in most peoples heads) be 1 month apart.

For most processing that happens monthly on the same day of the month e.g. premium collection, companies usually deal with the different numbers of days and leap years by saying no processing will take place on the 29th, 30th, or 31st.

I incorrectly thought that Datediff would work when asking for the number of months between 2 dates. What it does is calculate the number of month ends e.g. March 30th to April 2nd according to Datediff is 1 month apart which is complete rubbish.

Datediff does not work for this, so was looking for another solution.

I've found a workaround. When Datediff comes back with 1 month between March 30th and April 2nd I simply say if the day number in the from date (30th) is greater than the day number in the to date (2nd), I then subtract 1 from the answer Datediff gives.

Thanks for your response. Would still like to know if there is a better solution.

Would still like