I have a Date field in my object start_date__c. I need a formula field which deducts one month from startdate. Any idea how do i achieve this ?
-
a useful reference besides this forum, the SFDCSuccess forum is the SFDC Doc on useful formulas– cropredyJan 10, 2016 at 17:42
-
It's so complicated to have previous month date in formula field. Why do you need it in formula field and why you can't calculate it where you need one month before start_date?– Andrii MuzychukJan 10, 2016 at 17:50
-
I seriously didnt think it was so complicated, i thought there should be some function which could add or deduct a month like we have in apex.– PradyJan 11, 2016 at 5:07
Add a comment
|
1 Answer
Assuming you mean "One month" as the previous month, and same day (for the most part) then you could do something like this
CASE(
MONTH(object_start_date__c),
1, DATE(YEAR(object_start_date__c) - 1, 12, DAY(object_start_date__c)),
3,
IF(
DAY(object_start_date__c) < 29,
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c)),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 28)
),
5,
IF(
DAY(object_start_date__c) < 31,
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c)),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 30)
),
7,
IF(
DAY(object_start_date__c) < 31,
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c)),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 30)
),
10,
IF(
DAY(object_start_date__c) < 31,
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c)),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 30)
),
12,
IF(
DAY(object_start_date__c) < 31,
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c)),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 30)
),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c))
)
I think we can further simplify and cut down on the formula size. Maybe something like this
IF(
AND(
DAY(object_start_date__c) = 31,
OR(
MONTH(object_start_date__c) = 5,
MONTH(object_start_date__c) = 7,
MONTH(object_start_date__c) = 10,
MONTH(object_start_date__c) = 12
)
),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 30),
IF(
MONTH(object_start_date__c) = 1,
DATE(YEAR(object_start_date__c) - 1, 12, DAY(object_start_date__c)),
IF(
AND(
MONTH(object_start_date__c) = 3,
DAY(object_start_date__c) > 28
),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, 28),
DATE(YEAR(object_start_date__c), MONTH(object_start_date__c) - 1, DAY(object_start_date__c))
)
)
)
-
Thanks Chris.. i didnt think it was this complex, a simple addmonth kind of function should have been available– PradyJan 11, 2016 at 5:14
-
Yea, as you can see, some of the 'easy' stuff becomes more complex when you think out a few scenarios. Glad this helped though. Jan 11, 2016 at 12:40