Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question
    
a useful reference besides this forum, the SFDCSuccess forum is the SFDC Doc on useful formulas – cropredy Jan 10 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? – Chiz Jan 10 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 at 5:07
up vote 5 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.