+ Start a Discussion
sonninhosonninho 

Formula - Calculate a date in the future

Hi all,

 

I have a formula field (date type) to calculate a date that is 9 months in the future based on the issued_date__c (date type).

 

DATE ( YEAR(issued_date__c), MONTH(issued_date__c)+9, DAY(issued_date__c) )

 

This has failed completely if the month is April onward because there is no such thing as month 13.

 

Has anyone got a solution?

 

 

Thanks in advance,

 

S

 

SteveMo__cSteveMo__c

Does it have to literally be "9 Months" or would 270 Days (9 x 30) work?  

 

Doing the literal Month thing is going to be tricky, especially if your Issued Date (Day) is > 28 or 30 and the Issued Date (Month)  + 9 lands in February, or a month with < 31 days. 

 

So you're basically gonna have to calculate the Month of the Year, AND the Day of the Month for every possible Month/Day combination.  

 

So if the IssuedDate + 270  is a deal breaker for you, I see a LOT of calculations in your future...

sonninhosonninho

Hi Stevemo,

 

Unfortunately it has to be 9 months not 270 days. I was hoping that someone on here might have done a similar formula and can point me to the right direction.

SteveMo__cSteveMo__c
Is your Issue Date always the same day, like 1st of the month, or 15th of the month?  or could it possibly be 29, 30, 31?
sonninhosonninho
It is usually March 31st however it could be any date.
SteveMo__cSteveMo__c

What is the reason why Issued Date + 270 Days won't work? 

 

What do you guys do if the Issue Date + 9 months lands on Feb, April, June, Sept, Nov and the Issued Date (Day) is 31?

sonninhosonninho

Cheers Stevemo for your suggesstion. I have to opt for a trigger in the end, it is much easier to deal with the Date function in Apex (not the best solution but it works as I wanted). For those who are curious, i'm using addMonths() method.

Clueless in PetalumaClueless in Petaluma

Hi -- I actually have to calculate a date 270 days from an eligibility date -- do you have that formula?  Thanks

SteveMo__cSteveMo__c

Are you trying to populate a Formula(Date) field? 

If you are a formula like this should work for you

 

 

Date_Field + 270

Date_Field = the API Name of your "Eligibility Date" field

 

 

Let me know if it works for you or you need any help.

 

Steve

Clueless in PetalumaClueless in Petaluma

Hi Stevemo -- forgive me for being so stymied by formulas.  They look like a foreign language that I've never studied!  I tried your formula but it didn't work.  Here's the situation:  we have a field called: SVI Eligibility Date and a field called: SVI Expiration Date.  We would like the Expiration date field to be populated with a date 270 days from the Eligibility Date. I have the 'data type' on the expiration field as a date.  should it be auto number?  Your help is very much appreciated.

SteveMo__cSteveMo__c

Okay, what you need to do is create a new custom field 

 

Datatype: Formula(Date)

Formula: SVI_Eligibility_Date__c + 270  

 

But if you need to allow your users to override that date a Formula(Date) field won't work for you because they can't be edited.  If you need to allow someone to override the Exp Date then we'll have to make a few changes  

 

Oh yeah, and you owe me a beer! (these terms are non-negotiable)

http://beeradvocate.com/beer/profile/863/7971

Clueless in PetalumaClueless in Petaluma

I wish I could tell you that your formula worked but I failed to launch it and so did my associate.  I bought you a case of beer but, in our extreme frustration, we drank it all.....(burp).  I thank you for your efforts, though.

SteveMo__cSteveMo__c

What exactly are you guys having problems with?  Are you getting an error message?  Is the expiration date not updating?  

I have tested this on my DE SFDC.org and it works fine.  

Can you post the formula that you are using or a screenshot with the list of all of the fields and datatypes?

SteveMo__cSteveMo__c

Are you guys all set or do you still need help?

Clueless in PetalumaClueless in Petaluma

My supervisor felt we were spending too much time on this formula and since we couldn't get your formula to work, we've shelfed it -- i appreciate your help but "Lucy Loves Chocolate" and I don't have the experience and brain width to accomplish this.  If you're ever in Petaluma, CA, come on over and show us how to create the formula -- we'll take you to Petaluma's own beer pub, Lagunitas Brewing Co., for a pint.

SteveMo__cSteveMo__c

Are you serious?  What was the problem? (you guys never responded to my questions) did you get an error message or did the Expiration Date not calculate properly?  

Setting up a custom Formula Field like the one I posted should only take about 15 minutes (at the most).

Clueless in PetalumaClueless in Petaluma

Yes, we get a syntax error.  when i corrected it...or thought I did..the formula doesn't work.  I'd have to go back and input your formula to get the exact message, but essentially SF won't accept it.

SteveMo__cSteveMo__c

My gut tells me that you are trying to put my formula into one of your existing Date fields (and that won't work)

 

If you go back to my original post I said to create a NEW field selecting the datatype: Formula and Formula Return Type: (Date)

 

Then use my Formula 

 

SVI_Eligibility_Date__c + 270 

 Did you try doing that or did you do something else???

 

 

Clueless in PetalumaClueless in Petaluma

I did try that -- didn't work -- I know we're missing a step or two (or three), we're just not sure what it is -- you said it should take no more than 15 minutes to do.  I did it in less than 3 minutes -- that's why I think we're missing a few or more steps.  When I have time, I'll follow your new instructions and let you know what happens.  thanks for all your help and determination.

SteveMo__cSteveMo__c

Can you post a screenshot of the custom Field configuration that you are using along with the Formula?

Clueless in PetalumaClueless in Petaluma

Sorry i haven't gotten back to you -- i work here part-time and I've been busy with other work. I'll send you the screen shot when i can focus on the formula -- after thanksgiving 'cause I'm going out of town.  have  a great Thanksgiving.