Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Formulas and dates Post Reply Post New Topic
Author Message
brian@assyst
Newbie
Newbie


Joined: 19 Jun 2013
Location: United Kingdom
Online Status: Offline
Posts: 7
Quote brian@assyst Replybullet Topic: Formulas and dates
    Posted: 17 May 2018 at 3:37am
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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2018 at 5:17am
Please give an exact definition of what "months between dates" means. I find that usually there is no good definition of this and if you ask the person requesting the report they cannot define it. Maybe just count in 30 day buckets, not actual months?

Does Feb 28 to March 28 = 1 month or no months? That is 28 days difference which covers Feb but not March. What if it is a leap year?
IP IP Logged
brian@assyst
Newbie
Newbie


Joined: 19 Jun 2013
Location: United Kingdom
Online Status: Offline
Posts: 7
Quote brian@assyst Replybullet Posted: 18 May 2018 at 5:36am
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

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2018 at 6:52am
Not sure if you are attempting to be snide but I will assume,for the moment, that I just read it that way. Using lines like 99% of the population and "most peoples heads" is a bit condescending to someone offering you help. 30 day aging can also be a standard so it was not a left field option.

What I was attempting to get more clarification on what you are really asking for and using one example. I still don't know how you are counting a "month" as something other than what it as the 1st through last day of that calendar month.
If you are happy with your solution, great. If not, I would need to understand what 'a month' really means when you can start the clock on any day and months.
Be aware, if I understand your solution correctly, things like 1-31 to 2-28 will return 0 months.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.061 seconds.