In the past 20 years thousands of my clients have taken one or more Crystal classes. They are using those acquired skills to build amazing reports that show their data in meaningful ways. They are building reports with amazing formulas, giving them the answers they need.
Formulas are easier to build in Crystal than almost any other platform. That being said it can still get confusing and occasionally frustrating to come up with a formula that delivers the answer needed. I have often overthought the process and ended up writing a complex formula that wasn’t needed. You may feel the same way. It’s common to not use the power of Crystal and it’s built in functions, instead relying on brute force.
Today I’ll show you a formula that produces an often needed answer with minimal work.
How to come up with the last day of a given month. At first thought that might seem simple, but with months ending on the 28th, 30th & 31st of the month, not to mention leap-year when a month will end on the 29th, it can get difficult, real fast.
Many of us have written out a long IF-THEN-ELSE formula accounting for each month and hoping leap year is still several years off. First I’ll show you the formula, then explain. Notice that in the chart below a list of entries are shown with their accounting date. To the right of the accounting date are the results of the formula I’m about to show you.
Accounting Date Last Day Of The Month Formula Results
02/07/2020 02/29/2020
03/06/2020 03/31/2020
06//21/2020 06/30/2020
10/18/2020 10/31/2020
Last Day Of the Month Formula:
Local DateVar MonthEnd := Date ( DataAdd ("m",1, {YourDatabase.Accounting_Date} )) ;
MonthEnd - Day (MonthEnd)
At first this might be confusing but after I explain it--you’ll love it. Put any date value from within your database or a parameter and place it where I have Accounting Date. Doing just that, and you’ll always get the last day of that month. For example if the Accounting date is June 21st. 2020 then you’ll get the answer back June 30th, 2020. Notice it can even deal with Leap Year.
This formula takes whatever date you provide and it adds one month to it. So if you supply a date of May 15th, it first adds one month to it, returning June 15th. DAteAdd is a function that allows you to easily add or subtract months, days, weeks and even years to a given date. The second sentence above says take this new date (June 15th) and subtract 15 from it. If you take June 15th and count backwards 15 days you’ll get the last day of the prior month. The Day function used on the second line turns a date into a number that represents the day of the month.
I should explain the use of Local DateVar. Local DateVar is an easy way to hold on to a given date so it can be used later in the same formula. That is what we did in the above example. We computed a date one month in advance of our accounting date and gave it a name so we could remember it. The name we gave it was “MonthEnd”. Local DateVar simply says hold on to this date, it will be needed later. Very similar to hitting the memory key on a calculator. It holds a value for later use.
Sometimes the easiest way is the best way! I’ve used formulas much more difficult than the above one over the years to find the last day of the month. The beauty of Crystal’s formula language is the ease by which you can do so much. Hopefully you’ll never struggle again to find a given months end date. You can copy the formula above into your copy of Crystal and replace the {YourDatabase.Accounting_Date} with the Accounting Date from your table and it will work every time.