Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
HarmpieHarmpie 

Calculate a date using formula field

Is something like this possible using a formula field? I want to add a numberfield (number of months in 'Estimated_Time_For_Exection__c') to a start date, to calculate the end date.
 
The formula below gives an error:
 
Code:
DATE(YEAR(Start_Date__c),MONTH(Start_Date__c)+ Estimated_Time_For_Exection__c ,DAY(Start_Date__c))

 
Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell
DATE (
     FLOOR(((YEAR( Start_Date__c )  * 12) + MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c) / 12)
     -
     IF(MOD(MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12) = 0, 1, 0),
     IF(MOD(MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12)),
     28
     )
 
 
Replace 28 with whatever day of the month you want to default to.  Keep in mind if you go higher than 28 and it falls on February then you will recieve an error.  If you mark it as 31 and it falls on February, April, June, September or November you will get an error.
 
In order to pull the day from the Start Date field you need to create a custom s-control as the formula will compile too large otherwise.  Let me know if you want to do that,  I can post the s-control.
 
Hope this helps.
 

All Answers

BuellBuell
DATE (
     FLOOR(((YEAR( Start_Date__c )  * 12) + MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c) / 12)
     -
     IF(MOD(MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12) = 0, 1, 0),
     IF(MOD(MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + Estimated_Time_For_Exection__c,12)),
     28
     )
 
 
Replace 28 with whatever day of the month you want to default to.  Keep in mind if you go higher than 28 and it falls on February then you will recieve an error.  If you mark it as 31 and it falls on February, April, June, September or November you will get an error.
 
In order to pull the day from the Start Date field you need to create a custom s-control as the formula will compile too large otherwise.  Let me know if you want to do that,  I can post the s-control.
 
Hope this helps.
 
This was selected as the best answer
EmsEms

Hi there,

 

I would like to see the custom S control.  The math I am attempting is slightly easier, I simply want to add 1 year to a date. I'm getting the error about leap years and can't seem to fix it on my own.

 

Any help would be appreciated.

Thanks,

E
BuellBuell
No S-Control needed for that:

DATE(
YEAR (AppDate__c) + (FLOOR((TODAY() - AppDate__c) / 365)  + 1),
MONTH(AppDate__c),
IF(AND(DAY(AppDate__c) = 29,MONTH(AppDate__c ) = 02) , 28, DAY(AppDate__c)))



Just replace AppDate__c with whatever your initial date is.
JakesterJakester
No good deed goes unharassed :smileyvery-happy:

Could you please do one more of these?

I'd basically like the opposite of the first formula you made that added months to a date. I want to subtract months from a date. The good news is the day of the date should always be the 1st, so that part is easy.

My fields are Expiration_Date__c (a date) and Service_Months__c (a number) and Effective_Date__c (the date formula I need help with)

For example:

Exp Date Svc Months Eff Date
1/31/06     12                2/1/05
10/31/07    11               10/1/06
5/31/10      36               6/1/07
1/30/08       1                1/1/08
5/15/09       20              10/1/07

Thank you!!!
BuellBuell
Oh I'm always up for a good challenge, and for you Jakester, anything...

Code:
DATE (
     FLOOR(((YEAR( Expiration_Date__c )  * 12) + MONTH ( Expiration_Date__c ) - Service_Months__c) / 12)
     -
     IF(AND( DAY( Expiration_Date__c ) < 30,MOD(MONTH ( Expiration_Date__c  ) - Service_Months__c,12) = 0), 1, 0),
     IF(MOD(MONTH ( Expiration_Date__c ) - Service_Months__c + IF(DAY(Expiration_Date__c)  >= 30, 1,0),12) = 0, 12, MOD((YEAR( Expiration_Date__c ) * 12) + MONTH ( Expiration_Date__c ) - Service_Months__c + IF(DAY(Expiration_Date__c)  >= 30, 1,0),12)),
     1
     )

 
JakesterJakester
You are a freakin' genius!!
 
Thank you so much - I spent most of the day banging my head against this and getting nowhere. Your solution works beautifully, and you rock!!
raxrax

i have the same problem that i want to add the month to the start_date ?

can you please provide me the s-control code ? and how can i create it?