Authored by Zishan Razzaq

Wednesday, January 20, 2010

Determine End of Month Date or Days in Salesforce.com

Scenario:
   A) Determine how many days in a month.
   B) Determine how the End of Month Date

Solution:
For A:
   DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1) <== This will determine the days in the current Month.
For example: For Jan there are 31 Days in the month.

For B:
DATE(YEAR(TODAY()) ,MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

This will return the actual end of month date...
For January it will return January 31, 2010.
This does not accomodate Leap Year:
So an easier way to do this would be:
DATE(
YEAR(TODAY()),
MONTH(TODAY()),
CASE( MONTH(TODAY()),
1, 31,
2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28),
3, 31,
4, 30,
5, 31,
6, 30,
7, 31,
8, 31,
9, 30,
10, 31,
11, 30,
12, 31,
0
)
)

This will return for the Month of Feb: Feb, 28, 2010.

Now to get Last Day of Next Month it would be similiar:
DATE(
YEAR(TODAY()),
MONTH(TODAY())+1,
CASE( MONTH(TODAY())+1,
1, 31,
2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28),
3, 31,
4, 30,
5, 31,
6, 30,
7, 31,
8, 31,
9, 30,
10, 31,
11, 30,
12, 31,
0
)
)

This will Return The Next Month Like March:
March 31, 2010.
Now if you are using a Date field to determine the end of that current month and using a formula field to do so...
Substitute the TODAY() with the field that you want the formula to go off from...

Thank you
Check out my Other Blogs:

VisualForce Made Easy
Data Migration Made Easy
eTechCareers.com  Coming Soon!!!! All of your Employment needs for Information Technology and Project Management.
LINKS: