You need to sign in to do that
Don't have an account?
sonninho
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
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...
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.
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?
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.
Hi -- I actually have to calculate a date 270 days from an eligibility date -- do you have that formula? Thanks
Are you trying to populate a Formula(Date) field?
If you are a formula like this should work for you
Date_Field = the API Name of your "Eligibility Date" field
Let me know if it works for you or you need any help.
Steve
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.
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
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.
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?
Are you guys all set or do you still need help?
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.
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).
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.
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
Did you try doing that or did you do something else???
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.
Can you post a screenshot of the custom Field configuration that you are using along with the Formula?
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.