A simple formula for date return of last day of current month? - Answers - Salesforce Trailblazer Community
Ask Search:
RAKESH GUPTARAKESH GUPTA 

A simple formula for date return of last day of current month?

Best Answer chosen by RAKESH GUPTA
Sourav DuttaSourav Dutta
Hi Rakesh,

Try This:
IF( MONTH( TODAY() ) = 12, 
DATE( YEAR( TODAY() ), 12, 31 ), 
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1)

Best Regards,
Sourav​

All Answers

RAKESH GUPTARAKESH GUPTA

Can it be made shorter?

 

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 
    )
)

Sourav DuttaSourav Dutta
Hi Rakesh,

Try This:
IF( MONTH( TODAY() ) = 12, 
DATE( YEAR( TODAY() ), 12, 31 ), 
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1)

Best Regards,
Sourav​
This was selected as the best answer
Naveen DhanarajNaveen Dhanaraj
Simply Like This,
IF(
  MONTH( date ) = 12,
  DATE( YEAR( date ), 12, 31 ),
  DATE( YEAR( date ), MONTH ( date ) + 1, 1 ) - 1 
)
For More Info:
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0
 
Telmo BatistaTelmo Batista
New way:

ADDMONTHS(DATE(YEAR(TODAY()),1,31),MONTH(TODAY()) -1 )

Best Regards
Marc BradyMarc Brady

Hello, I had workflow based on this formula working perfectley fine until this month (December). Now I get this error:

Formula evaluation error in Custom Link Renewal
Month or Day out of range in DATE() function

My formula is: DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1

I even tried Telmo's formula above but to no avail

Can anyone help please? 

Telmo BatistaTelmo Batista
I Marc,

Your formula is breaking in year.

What do you mean with "I even tried Telmo's formula above but to no avail" ??
Marc BradyMarc Brady
I tried this formula: 

ADDMONTHS(DATE(YEAR(TODAY()),1,31),MONTH(TODAY()) -1 )

How do I rectify the year? Is it because its going into 2019? 
Telmo BatistaTelmo Batista
It's working for meUser-added image
Telmo BatistaTelmo Batista
User-added image
Marc BradyMarc Brady
Works for me now! How weird, kep on chucking that error at me this morning 

Cheers!