# How to deduct one month from a date in a formula field

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 – 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? – Andrew Muza 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

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 – 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