You need to sign in to do that
Don't have an account?

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
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
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
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)
the text field i set the value as March - 2017
but the formula field gave 30/6/2017
which should be 1/3/2017
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.
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
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)
i entered march - 2017
date is 30/6/2017
which should be 1/1/2017
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)
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
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)