5

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 ?

Improve this question
3
  • a useful reference besides this forum, the SFDCSuccess forum is the SFDC Doc on useful formulas
    – cropredy
    Jan 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 Muzychuk
    Jan 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.
    – Prady
    Jan 11, 2016 at 5:07

1 Answer 1

Reset to default
5

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))
          )
     )
)
Improve this answer
2
  • Thanks Chris.. i didnt think it was this complex, a simple addmonth kind of function should have been available
    – Prady
    Jan 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.
    – Chris Duncombe
    Jan 11, 2016 at 12:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .