Formulas and dates
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22601
Printed Date: 28 Apr 2024 at 1:38pm
Topic: Formulas and dates
Posted By: brian@assyst
Subject: Formulas and dates
Date 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
|
Replies:
Posted By: DBlank
Date 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?
|
Posted By: brian@assyst
Date 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
|
Posted By: DBlank
Date 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.
|
|