+ Start a Discussion
mw6mw6 

how to populate the last date of this to a date field using formula

I have a field called 'Expiry_Date__C.  Can anybody help me to write a formula to populate the last date of this to a date field using formula
Best Answer chosen by mw6
Amol Salve 13Amol Salve 13
Hello,
Create formula field with eturn type date and use following code 
 
DATE
(
	YEAR( Start_Date__c ),
	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 
	)
)

Thank you,
Amol Salve
Salesforce developer 

All Answers

Amol Salve 13Amol Salve 13
Hello,
Create formula field with eturn type date and use following code 
 
DATE
(
	YEAR( Start_Date__c ),
	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 
	)
)

Thank you,
Amol Salve
Salesforce developer 
This was selected as the best answer
mw6mw6
Thanks Amol,
I tried and it seems the date is showing 6 months later.  I want to populate 31/12/2017 to expiry date when a record is created, is there any way
 
Shiva RajendranShiva Rajendran
Hi Mw6,
You mean ,you want to set the last day of the years as in the start date? or you want to add 1 year to the start date as expiry date?

To just set the last day of the same year,
DATE
(
	YEAR( Start_Date__c ),
	Month( 12),Day(31)
	
	
)

To add one year to the start date
 
​DATE ( YEAR( Start_Date__c )+1, Month( Start_Date__c ),Day(Start_Date__c ) )


Thanks and Regards,
Shiva RV
Amol Salve 13Amol Salve 13
Hello 
change Start_date__c with you expiry date fieid..the above code is working fine in my fornula field

Thank you 
Amol Salve
Salesforce Developer
mw6mw6
I just want to populate the last date of this year '31/12/2017' into the expiry_date__c field.  is it possible via formula or trigger
User-added image
mw6mw6
Thanks Amol