function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
John CollierJohn Collier 

formula to generate date for last day of previous month, one year later

We need the formula to generate a date for the last day of the previous month, one year from now. I have tried the formulas in posts for last day of previous month and for last day of this month one year from now, but I have not been able to modify and have the formula accepted.
Alain CabonAlain Cabon
You should give examples for: "a date for the last day of the previous month, one year from now."

For example: 4 september 2018 : one year from now = 4 september 2017 , last day of the previous month = 31 august 2017

but 2 january 2018  :  one year from now = 2 january 2017 , last day of the previous month = 31 decembre 2016

One year form now will be always: YEAR (date) - 1 but previous month, will be : MONTH ( date)  - 1 and if MONTH ( date ) = 0 then MONTH(date) = 12 and YEAR ( date ) - 2

With two formulas, it is easier.

1) Formula: date_previous__c : 
IF ( MONTH ( date1__c ) - 1 = 0, DATE ( YEAR ( date1__c ) - 2, 12 , 1 ), DATE ( YEAR ( date1__c ) - 1, 
MONTH ( date1__c )- 1 , 1 ))

2) Formula : Finding just the Last Day of the Month: date_last_day__c (using  date_previous__c )
The easiest way to find the last day of a month is to find the first day of the next month and subtract a day. 
IF(
  MONTH( date_previous__c ) = 12,
  DATE( YEAR( date_previous__c ), 12, 31 ),
  DATE( YEAR( date_previous__c ), MONTH ( date_previous__c ) + 1, 1 ) - 1 
)

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5
Alain CabonAlain Cabon
I have probably misunderstood "one year from now" ( next year and not previous year )
That's why you should give examples for: "a date for the last day of the previous month, one year from now."

For example: 4 september 2018 : one year from now = 4 september 2019 , last day of the previous month = 31 august 2019

but 2 january 2018  :  one year from now = 2 january 2019 , last day of the previous month = 31 decembre 2018

One year form now will be always: YEAR (date) + 1 but previous month, will be : MONTH ( date)  - 1 and if MONTH ( date ) = 0 then MONTH(date) = 12 and YEAR ( date ) 

With two formulas, it is easier.

1) Formula: date_previous__c :
  
IF ( MONTH ( date1__c ) - 1 = 0, DATE ( YEAR ( date1__c ), 12 , 1 ), DATE ( YEAR ( date1__c ) + 1, 
MONTH ( date1__c ) - 1 , 1 ))

2) Formula : Finding just the Last Day of the Month: date_last_day__c (using  date_previous__c )
The easiest way to find the last day of a month is to find the first day of the next month and subtract a day.
  
IF(
  MONTH( date_previous__c ) = 12,
  DATE( YEAR( date_previous__c ), 12, 31 ),
  DATE( YEAR( date_previous__c ), MONTH ( date_previous__c ) + 1, 1 ) - 1 
)

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5