How can you find the last day of any month for reporting purposes? Perhaps you need a report to check account running balances for the last day of the month over the last 12 months. Or maybe you want to see the sales closing numbers, for the last day of the month over the past year. These sorts of reports can be very helpful, but considering each month has a different number of days, how do you find the answers?
Well the answer is actually a pretty simple trick. You simply find the first day of the next month, and subtract one day. The system will automatically take a step back on the calendar and give you the results you need.
When plugged into a formula field, the result should look something like this:
MONTH( date ) = 12,
DATE( YEAR( date ), 12, 31 ),
DATE( YEAR( date ), MONTH ( date ) + 1, 1 ) – 1
Presto! You are now looking at the last date of the month. You can use this to get more creative, like creating formula check boxes if a certain record falls on the last day of the month, to create a report to see what the performance was ONLY on the last day of the month
-Ryan and the CloudMyBiz Team