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
sfdc dev 2264sfdc dev 2264 

Salesforce Formula field help needed

Hi ,

I need help on the following requirement below as follows,

I have a text field called as "Latest Period date" which stores the value as March - 2017

I want another formula field which should capture a value which is 3 months prior to latest period date

for eg

if Latest period date is March -2017

then the formula field should hold Jan - 2017

if its jan 2017 

then the formula field should hold Oct - 2016

Kindly help me with the formula please

Thanks in Advance
Best Answer chosen by sfdc dev 2264
kavya rao neelagirikavya rao neelagiri
Hi,

I think the 'Latest Period date' field data type should be Date instead of text (will be issues if there are typos).
Create a formula field with 'date' data type and add this formula
DATE(YEAR(Latest_Date__c),MONTH(Latest_Date__c)-3,DAY(Latest_Date__c))

Let me know if you have any issues
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

All Answers

Mahesh Dhara 9Mahesh Dhara 9
Try this
case(month(today()),
1, date(year(today()), 03,31),
2, date(year(today()), 03,31),
3, date(year(today()), 03,31),
4, date(year(today()), 06,30),
5, date(year(today()), 06,30),
6, date(year(today()), 06,30),
7, date(year(today()), 09,30),
8, date(year(today()), 09,30),
9, date(year(today()), 09,30),
10, date(year(today()), 12,31),
11, date(year(today()), 12,31),
12, date(year(today()), 12,31),
null)
sfdc dev 2264sfdc dev 2264
I tried the above formula but its giving a rong value

the text field i set the value as March - 2017

but the formula field gave 30/6/2017

which should be 1/3/2017
kavya rao neelagirikavya rao neelagiri
Hi,

I think the 'Latest Period date' field data type should be Date instead of text (will be issues if there are typos).
Create a formula field with 'date' data type and add this formula
DATE(YEAR(Latest_Date__c),MONTH(Latest_Date__c)-3,DAY(Latest_Date__c))

Let me know if you have any issues
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
This was selected as the best answer
sfdc dev 2264sfdc dev 2264
Hi ,

its giving me this error on trying above formula


DATE(YEAR(Latest_Period_Date__c    ),MONTH(Latest_Period_Date__c)-3,DAY(Latest_Period_Date__c))

 Error: Incorrect parameter type for function 'YEAR()'. Expected Date, received Text
 
Mahesh Dhara 9Mahesh Dhara 9
TRY THIS
case(month(today()),
1, date(year(today()-90), 03,31),
2, date(year(today()-90), 03,31),
3, date(year(today()-90), 03,31),
4, date(year(today()-90), 06,30),
5, date(year(today()-90), 06,30),
6, date(year(today()-90), 06,30),
7, date(year(today()-90), 09,30),
8, date(year(today()-90), 09,30),
9, date(year(today()-90), 09,30),
10, date(year(today()-90), 12,31),
11, date(year(today()-90), 12,31),
12, date(year(today()-90), 12,31),
null)
kavya rao neelagirikavya rao neelagiri
You are getting that error becuase the Lates_Period__c in your object is text. Is there a way you can change it to Date data type
sfdc dev 2264sfdc dev 2264
No , unfortunately that is referred in case object as well , so not able to change the field type
sfdc dev 2264sfdc dev 2264
i tried theabove formula as well , but no luck the same 

i entered march - 2017

date is 30/6/2017

which should be 1/1/2017
Mahesh Dhara 9Mahesh Dhara 9
the previous one and this one different if u tried this one leave it
sfdc dev 2264sfdc dev 2264
i tried this one only , but still same

case(month(today()),
1, date(year(today()-90), 03,31),
2, date(year(today()-90), 03,31),
3, date(year(today()-90), 03,31),
4, date(year(today()-90), 06,30),
5, date(year(today()-90), 06,30),
6, date(year(today()-90), 06,30),
7, date(year(today()-90), 09,30),
8, date(year(today()-90), 09,30),
9, date(year(today()-90), 09,30),
10, date(year(today()-90), 12,31),
11, date(year(today()-90), 12,31),
12, date(year(today()-90), 12,31),
null)
sfdc dev 2264sfdc dev 2264
Kavya 

can we create a formula field to store that text field value to date and use your second formula

for eg

March - 2017 

to be stored as 1/3/2017

and then i can use your formula which works 

Let me know 
Mahesh Dhara 9Mahesh Dhara 9
Use DATEVALUE(expression)-----to convert text value to date value
sfdc dev 2264sfdc dev 2264
you mean DATEVALUE(Latest_Period_Date__c    )

 
sfdc dev 2264sfdc dev 2264
i tried the above it displays as error when i enter march -2017
kavya rao neelagirikavya rao neelagiri
Yes, you can convert it. There is a possibility to get Error when the text field you enter is invalid
Please look at this https://success.salesforce.com/answers?id=90630000000gvfiAAA.

For example: in text field you may enter march 25th 2017 or 3/25/2017 (there can be typo too like : mrch 25th 2017 or 13/25/2017 which is invalid date)
sfdc dev 2264sfdc dev 2264
Thanks kavya for the much needed help , i will have a look at it , Thanks