ShowAll Questionssorted byDate Posted
DRPowerUser

# Future Date Formula Based on Existing Date

I am needing assistance with calculating a future date based on the current date + __ Months.  For example:

Current Date = 12/15/2009

# Future Months = 6

Future Date =  06/15/2010

In Excel, the formula is = DATE(YEAR(12/15/2009),MONTH(12/15/2009)+6,DAY(12/15/2009)

What would the formula be in Salesforce to calculate the future date (e.g. 06/15/2010)?

shillyer

If current date is today's date, try TODAY() + 180

Or just try current date + 180

Hope that helps,

Sati

Sebastian75

Just a thought here=> TODAY()+180 isn't totally accurate because the number of days in a month varies depending on the month.

Something approaching would be

IF(
MONTH( CloseDate)<=6,
DATE(YEAR(Date),MONTH(Date)+6,DAY(Date)),
DATE(YEAR(Date)+1,MONTH(Date)-7,DAY(Date) ) )

But there is a catch in this that would not work in the above formula, if today is the 31/05/10, this would return an eroor as th 31/11/10 does not exist. So basically, you should enhancethe above formula to add some processing on the day number in a given month.

Easiest way of course would be to handle this with a trigger and simply use the Apex Date.AddMonths() method...

Message Edited by Sebastian75 on 01-26-2010 06:26 PM
Message Edited by Sebastian75 on 01-26-2010 06:27 PM
DRPowerUser

I don't think this would work since I didn't clarify that the # of Months = 6 is a picklist field.

We have contract lengths that could be 3,6,9,12,15,18,24 months.  I've setup a picklist field so in essence, the # of months can't be hard-coded into the formula.

shillyer

You can do this then:

TODAY() + ( VALUE(TEXT( Contract_Length__c )) * 30)

Hope that helps,

Sati