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
Chris HighChris High 

Field update to calculate LAST DAY of the month, 1 year later.

I found some posts regarding adding a year to a date, and even calculating the FIRST day of a following month. But my effort to reverse engineer these was fruitless. For our contracts, whatever a rep enters as the START date, we want the END date to auto-populate (upon saving) for one year later, but the LAST DAY of whatever that month happens to be.

So I need it to calulate DATE of Start_date__c + 1 year, + 1 month, minus 1 day

That's do-able right? Thanks in advance!!
 
Best Answer chosen by Chris High
William TranWilliam Tran
This should do it:
  
DATE(YEAR(Start_date__c) +1, MONTH(Start_date__c) + 1, 1) - 1
As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

Thanks

All Answers

William TranWilliam Tran
Can you provide examples?  

start date = 2/2/2015,  end date =?
start date = 3/1/2015,  end date =?
start date = 4/30/2015,  end date =?
start date = 1/1/2015,  end date =?

Thx
Chris HighChris High
start date = 2/2/2015,  end date = 2/29/2016
start date = 3/1/2015,  end date = 3/31/2016
start date = 4/30/2015,  end date = 4/30/2016
start date = 1/1/2015,  end date = 1/31/2016
William TranWilliam Tran
Is this a formula field?  Or are you using APEX code? Thx Sent from Yahoo Mail on Android
William TranWilliam Tran
This should do it:
  
DATE(YEAR(Start_date__c) +1, MONTH(Start_date__c) + 1, 1) - 1
As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

Thanks
This was selected as the best answer
Neetu_BansalNeetu_Bansal
Hi Chris,

You can use this formula to fulfil your requirement. Create a Formula field with return type as Date and use this: 
DATE
(
	YEAR( Start_Date__c ) + 1,
	Month( Start_Date__c ),
	CASE( MONTH(Start_Date__c), 
		1, 31, 
		2, IF( MOD( YEAR(Start_Date__c) + 1, 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 
	)
)

Let me know, if you need any other help.

Thanks,
Neetu
Chris HighChris High
William, thank you! That's simple and worked perfectly. And as expected, using -1 on the day forces it to use calendar logic instead of having to hard-code each possible end date (naturally I tested with some Februaries as well)
Susanna SharpSusanna Sharp
Chris, last year you asked the exact question I need an answer to. Can you help? I tried William's formula, but I'm using this in my Opportunity object to read from the Closed Won field. How do I edit (Start_date__c) to reflect Closed Won? I tried (Closed_Won__c) and (Closed_Won)...I'm new to formulaland and need to find a Trailhead I guess... 
Christian WeatherfordChristian Weatherford
I believe William's solution will fail if Start Date happens to be in December. It would result in a "13" for the month portion since that part of the formula doesn't know to increment the year and start the month portion back at 1.
IT Main OneIT Main One
This should do the trick:

1. If you want to arrive at the next day.
IF(MONTH([-- DATE --]) < 12, 
DATE(YEAR([-- DATE --]), MONTH([-- DATE --]) + 1, 1), 
DATE(YEAR([-- DATE --]) + 1, 1, 1))

2. If you want to arrive at the exact day
IF(MONTH([-- DATE --]) < 12, 
DATE(YEAR([-- DATE --]), MONTH([-- DATE --]) + 1, 1), 
DATE(YEAR([-- DATE --]) + 1, 1, 1)) - 1
Wes Trent 3Wes Trent 3
I am also trying to return the last day of the same month plus one year, and even though the syntax is correct, it turns a blank value on the date field.

Someone please help?

DATE
(
YEAR(DATEVALUE([Case].CreatedDate))+1,
MONTH(DATEVALUE([Case].CreatedDate)),
CASE(MONTH(DATEVALUE([Case].CreatedDate)),
1, 31,
2, IF(MOD(YEAR(DATEVALUE([Case].CreatedDate)), 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
)
)
Kayla Even 8Kayla Even 8
I am trying to calculate the end of the first year of a contract. So for example, if Start date is 11/1/2020 and contract end date is 9/1/2024, I want the first year end date to be 8/31/2021 since the first year would have been prorated to then align with the full year for year 2 and year 3 of the contract. Is that possible?
Rander GamaRander Gama
I had problems using the "William Tran" formula, it crashed when the month in December. Here's a new suggestion not to make a mistake when we're in December and moving on to next year.

Eu tive problemas em usar a formula do "William Tran", ela deu erro quando o mês em dezembro. Segue uma nova sugestão para não dar erro quando estivermos no mês de dezembro e avançamos para o próximo ano.

How to calculate the end day of the month:

DATE(YEAR(ADDMONTHS(Start_date__c, 1)), MONTH(ADDMONTHS(Start_date__c, 1)), 1) - 1
drrincondrrincon
@Rander Gama is kind of close, but I don't see the point of adding months to the YEAR formula... this is actually not returning the right value, since you are not actually adding any new year to the new date, so it will (in the best case) result in the last day of current's date month... This could eventually work if the ADDMONTHS in the YEAR() were adding 12 months instead of 1, but this is the long way to do something simpler...

To get last day of the month, of exactly one year from now, should be good enough to use the following formula: 
ADDMONTHS(DATE(YEAR(Start_date__c), MONTH(Start_date__c), 1),13) - 1

This will handle automatically any date inconvenient... and will compile with 164chars vs. the corrected formula above (179) which depending on where you will use this, it might matter.

Cheers.
Harish SAWARKAR 25Harish SAWARKAR 25
Here is the formula suggested in salesforce help doc
IF( MONTH( date ) = 12, DATE( YEAR( date ), 12, 31 ), DATE( YEAR( date ), MONTH ( date ) + 1, 1 ) - 1 )
https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5