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
mgibsonmgibson 

Trying to create a formula field for a date

Hi all!  I need to create a formula field and am getting stuck.  Here are the three fields to use:

Start_Date__c = date field
Frequency__c = number field
Period__c = picklist (month and year are the picklist values)

I need to create a new formula field (date) to take that start date, and display a new date which is the Start Date + Frequency * Period

So examples would be:

Start Date = 1/1/2022
Frequency = 1
Period = Year
Desired new field output = 1/1/2023

Start Date = 1/1/2022
Frequency = 1
Period = Month
Desired new field output = 2/1/2022

Can anyone recommend how to accomplish this?  Thanks!


 
Best Answer chosen by mgibson
Sai PraveenSai Praveen (Salesforce Developers) 
Hi,

Can you try using this formula. This will take leapyear also into consideration if we have to add the years.
  
If(Text( Period__c )='month', ADDMONTHS( Start_Date__c , Frequency__c ) ,IF(
AND(
MONTH( Start_Date__c ) = 2,
DAY( Start_Date__c ) = 29,
NOT(
OR(
MOD( YEAR( Start_Date__c ) + Frequency__c, 400 ) = 0,
AND(
MOD( YEAR( Start_Date__c ) + Frequency__c, 4 ) = 0,
MOD( YEAR( Start_Date__c ) + Frequency__c, 100 ) != 0
)
)
)
),
DATE( YEAR( Start_Date__c ) + Frequency__c, 3, 1),
DATE( YEAR( Start_Date__c ) + Frequency__c, MONTH( Start_Date__c ), DAY( Start_Date__c ) )
)
)

If this solution helps, Please mark it as best answer.

Thanks,
​​​​​​​

All Answers

Sai PraveenSai Praveen (Salesforce Developers) 
Hi,

Can you try using this formula. This will take leapyear also into consideration if we have to add the years.
  
If(Text( Period__c )='month', ADDMONTHS( Start_Date__c , Frequency__c ) ,IF(
AND(
MONTH( Start_Date__c ) = 2,
DAY( Start_Date__c ) = 29,
NOT(
OR(
MOD( YEAR( Start_Date__c ) + Frequency__c, 400 ) = 0,
AND(
MOD( YEAR( Start_Date__c ) + Frequency__c, 4 ) = 0,
MOD( YEAR( Start_Date__c ) + Frequency__c, 100 ) != 0
)
)
)
),
DATE( YEAR( Start_Date__c ) + Frequency__c, 3, 1),
DATE( YEAR( Start_Date__c ) + Frequency__c, MONTH( Start_Date__c ), DAY( Start_Date__c ) )
)
)

If this solution helps, Please mark it as best answer.

Thanks,
​​​​​​​
This was selected as the best answer
CharuDuttCharuDutt
Hii Mgibsion
Try Below Formula 
If(Text( Period__c )='month', ADDMONTHS( Start_Date__c , Frequency__c ) ,IF(
AND(
MONTH( Start_Date__c ) = 2,
DAY( Start_Date__c ) = 29,
NOT(
OR(
MOD( YEAR( Start_Date__c ) + Frequency__c, 400 ) = 0,
AND(
MOD( YEAR( Start_Date__c ) + Frequency__c, 4 ) = 0,
MOD( YEAR( Start_Date__c ) + Frequency__c, 100 ) != 0
)
)
)
),
DATE( YEAR( Start_Date__c ) + Frequency__c, 3, 1),
DATE( YEAR( Start_Date__c ) + Frequency__c, MONTH( Start_Date__c ), DAY( Start_Date__c ) )
)
)
Please Mark It As Best Answer If It Helps
Thank You!
mgibsonmgibson
Thank you both!  Worked great.  Much appreciated!!!