Easy formula for finding the last Day of the Month
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 screen print below a list of entries are shown with their accounting date. To the right of the accounting date is the results of the formula I’m about to show you.
First I’ll show you the formula, then explain.
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 GLT Current Transaction Accounting Date. Just doing that, and you’ll always get the last day of that month. For example if the Accounting date is May 15th, 2018 then you’ll get the answer back May 31st, 2018.
Basically 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.