Crystal Reports Tip: Easy Formula to Find Last Day of the Month

It’s hard to believe but more than 20 years have gone by since Sage started using Crystal Reports with its Sage 300 CRE product. At first, Sage used Crystal simply to produce quality Billing Invoices. But over the years Crystal reports made their way into practically every module. Now there are canned reports to produce Project Management documents, Job Cost Overview reports, Property Management Rent Rolls and so much more.

In this article, we’ll show you how to answer one of the most common questions we get about Crystal Reports … how to find the last day of a given month.

Building Formulas in Crystal Reports

In the past 20 years, thousands of Sage 300 CRE clients have taken one or more Crystal Reports Training 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 necessary. You may feel the same way. It’s common to not use the power of Crystal and its built-in functions, instead relying on brute force.

Today I’ll show you a formula that produces an often-needed answer with minimal work.

Finding the Last Day of the Month

A frequently-asked solution is 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 become a little more complex. Of course you could write out a long IF-THEN-ELSE formula accounting for each month and hoping leap year is still several years off. We’ve all written that formula.

First, I’ll show you the formula and 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.

Step by Step

At first this might be confusing but after I explain it, you’ll love it. Today I’ll show you an easy way to get the last day of the month (Any month). First I’ll show you the formula, then explain.

Put any date value from within Sage 300 Construction 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 a 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 which 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! Over the years, I’ve used formulas that are much more difficult than the one above in order 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.

With this handy tip, hopefully you’ll never struggle again to find a months end date again!

Article by
David Hardy
Consultant, Accordant Company

Need Crystal Reports Training?

Click below to check out our upcoming schedule of events and classes that will teach you everything you need to know about Crystal Reports from the very basics of reporting to highly advanced topics and techniques.