Tip of the Week – Reporting the Last Day of the Month in Salesforce
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:
IF(
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
Click here for more sample date formulas!
-Ryan and the CloudMyBiz Team
CEO of CloudMyBiz Salesforce CRM consulting services with a deep knowledge in the lending industry. Taking keen interest in the project management side of operations, playing a vital role in the 31% YOY company growth. Strategic leader, mastering the ability to problem solve at every level of the business, providing effective solutions for clients.