Calculate Warranty Expiration Date from picklist

I've been making custom fields for a while, but I haven't really gotten into formula's yet. But our company just switched from 12 month warranty to 14 month warranty...so that's a lot harder to calculate in your head after three or four years. I have two fields: Ship Date (Date field), and warranty length (Picklist). Is there an easy formula that I can create to calculate the warranty expiration from those two fields? or do I need to make a strict number field to make the calculation and just have a list somewhere that I refer to when determining the original length (since it gets to me as "one year, two years, three..." etc.

Any help would be greatly appreciated!

-Dan

May 12, 2009
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

Slangan
Ahhh yes. Very Correct - The fix for this gets a little wonky, so bear with my while I do my best to explain what I did. There may well be a more elegant way of doing this with apex, but this will work with the basic functionality of SF.

Updated formula:

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
      TEXT(YEAR( CloseDate )+1) & "-" &
      IF(MONTH(CloseDate)+2>12, TEXT(MONTH( CloseDate )+2-12), TEXT(MONTH(CloseDate)+2)) & "-" &
      TEXT(DAY( CloseDate )),
"28 Months - 2 Years",
      TEXT(YEAR( CloseDate )+2) & "-" &
      IF(MONTH(CloseDate)+4>12, TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+4)) & "-" &
      TEXT(DAY( CloseDate )),
"42 Months - 3 Years",
     TEXT(YEAR( CloseDate )+3) & "-" &
      IF(MONTH(CloseDate)+6>12, TEXT(MONTH( CloseDate )+6-12), TEXT(MONTH(CloseDate)+6)) & "-" &
     (TEXT(DAY( CloseDate ))),
"56 Months - 4 Years",
     TEXT(YEAR( CloseDate )+4) & "-" &
     IF(MONTH(CloseDate)+8>12,TEXT(MONTH( CloseDate )+8-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
"72 Months - 5 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     TEXT(MONTH(CloseDate)) & "-" &
     TEXT(DAY( CloseDate )),
"86 Months - 6 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     IF(MONTH(CloseDate)+4>12,TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
NULL))

What I did:

We needed to create a date calculation for months that run over 1 year. To do this, we need to understand that SF views months as numbers (1 = January, 2 = February, etc.). This means the biggest number value we can have in the months area is 12. To calculate the month when we get more than 12, we simply subtract 12 from the value to get the remaining months (or the number telling us how many months into the new year our warranty goes). This calculation can be achieved by an IF statement. IF the number of months in our Ship Date (IF(MONTH(CloseDate)+4>12,) is more than 12, subtract 12 from the total (MONTH(CloseDate)+4-12,), otherwise, display the value of the Ship Date (MONTH(CloseDate)+4). The full expression would look like this:

IF(MONTH(CloseDate)+4>12,MONTH(CloseDate)+4-12,MONTH(CloseDate)+4)

In order to use the IF statement, we had to convert all of our DATE datatypes to TEXT. This is because an IF statement can only be used with TEXT. I did this by enclosing all of my date values in the TEXT() function - TEXT() converts whatever is inside it to text.

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
      TEXT(YEAR( CloseDate )+1) & "-" &
      IF(MONTH(CloseDate)+2>12, TEXT(MONTH( CloseDate )+2-12), TEXT(MONTH(CloseDate)+2)) & "-" &
      TEXT(DAY( CloseDate )),
"28 Months - 2 Years",
      TEXT(YEAR( CloseDate )+2) & "-" &
      IF(MONTH(CloseDate)+4>12, TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+4)) & "-" &
      TEXT(DAY( CloseDate )),
"42 Months - 3 Years",
    TEXT(YEAR( CloseDate )+3) & "-" &
      IF(MONTH(CloseDate)+6>12, TEXT(MONTH( CloseDate )+6-12), TEXT(MONTH(CloseDate)+6)) & "-" &
     TEXT(DAY( CloseDate ))),
"56 Months - 4 Years",
     TEXT(YEAR( CloseDate )+4) & "-" &
     IF(MONTH(CloseDate)+8>12,TEXT(MONTH( CloseDate )+8-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
"72 Months - 5 Years",
     TEXT((YEAR( CloseDate )+6) & "-" &
    TEXT(MONTH(CloseDate)) & "-" &
    TEXT(DAY( CloseDate )),
"86 Months - 6 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     IF(MONTH(CloseDate)+4>12,TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
NULL))

Finally, because the field type is a DATE datatype, we had to convert our result to at DATE or it would show an #ERROR! in the field (remember, we converted everything to text to make it work with the IF statement). To do this I enclosed the entire formula in DATEVALUE(), which converts the text back to a DATE datatype.

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
      TEXT(YEAR( CloseDate )+1) & "-" &
      IF(MONTH(CloseDate)+2>12, TEXT(MONTH( CloseDate )+2-12), TEXT(MONTH(CloseDate)+2)) & "-" &
      TEXT(DAY( CloseDate )),
"28 Months - 2 Years",
      TEXT(YEAR( CloseDate )+2) & "-" &
      IF(MONTH(CloseDate)+4>12, TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+4)) & "-" &
      TEXT(DAY( CloseDate )),
"42 Months - 3 Years",
     TEXT(YEAR( CloseDate )+3) & "-" &
      IF(MONTH(CloseDate)+6>12, TEXT(MONTH( CloseDate )+6-12), TEXT(MONTH(CloseDate)+6)) & "-" &
     (TEXT(DAY( CloseDate ))),
"56 Months - 4 Years",
     TEXT(YEAR( CloseDate )+4) & "-" &
     IF(MONTH(CloseDate)+8>12,TEXT(MONTH( CloseDate )+8-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
"72 Months - 5 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     TEXT(MONTH(CloseDate)) & "-" &
     TEXT(DAY( CloseDate )),
"86 Months - 6 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     IF(MONTH(CloseDate)+4>12,TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
NULL))

The last thing I had to do was add some dashes "-" so that the results of our formula would be formatted properly for conversion to a DATE. To do this I used "&" which allows you to attach strings of text to one another to make a pieced-together word or sentence.

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
      TEXT(YEAR( CloseDate )+1) & "-" &
      IF(MONTH(CloseDate)+2>12, TEXT(MONTH( CloseDate )+2-12), TEXT(MONTH(CloseDate)+2)) & "-" &
      TEXT(DAY( CloseDate )),
"28 Months - 2 Years",
      TEXT(YEAR( CloseDate )+2) & "-" &
      IF(MONTH(CloseDate)+4>12, TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+4)) & "-" &
      TEXT(DAY( CloseDate )),
"42 Months - 3 Years",
     TEXT(YEAR( CloseDate )+3) & "-" &
      IF(MONTH(CloseDate)+6>12, TEXT(MONTH( CloseDate )+6-12), TEXT(MONTH(CloseDate)+6)) & "-" &
     (TEXT(DAY( CloseDate ))),
"56 Months - 4 Years",
     TEXT(YEAR( CloseDate )+4) & "-" &
     IF(MONTH(CloseDate)+8>12,TEXT(MONTH( CloseDate )+8-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
"72 Months - 5 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     TEXT(MONTH(CloseDate)) & "-" &
     TEXT(DAY( CloseDate )),
"86 Months - 6 Years",
     TEXT(YEAR( CloseDate )+6) & "-" &
     IF(MONTH(CloseDate)+4>12,TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
NULL))

As an added bonus, this new approach removed a lot of the CloseDate items that used up our character limits and allowed us to add more text without going over the 5000 character limit. That means you should now be able to add all of your warranties w/o problems.

Whew!

I hope this explains everything to you. If you have any questions - don't be afraid to ask!

Shannon
P.S. If this works as the solution to your question, please click the 'accepted solution' button.
Thanks!

May 14, 2009
·
Like
0
·
Dislike
0

Slangan
I think the code below will work.

CASE(Warranty_Length__c, "14 months", DATE(YEAR(TODAY())+1,MONTH(TODAY())+2,DAY(Today())), "12 months", DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(Today())), NULL)

Replace Warranty_Length__c with the name of your warranty length field.You willfind this field if you use the "insert field" button above the formula text area.
Replace the values inquotations (14 months, etc.) with the EXACT values you have in your picklist. If they are not exactly the same the code will not work (caps, spaces, etc.)
Convert your warranty time into years and months. So for 14 month warranty, it would be 1 year and two months.
In the first YEAR(TODAY())+1 area, replace the +1 with the year value of your first listed warranty and in the MONTH(TODAY())+2 area, replace the +2 with the monthvalue of your first listed warranty. For example, I used 14 months as my first value so that would be 1 in the year area and 2 in the months area.
Repeat with the second date value (I used 12 months, so it would be +1 in the year area and since there were no months I did not need to add any months to the month area.
Finally, decide what you want to display in your field if no warranty is selected. In this case I chose to leave the field blank, so I put NULL (the instruction for the computer to leave the field blank..
I hope I explained everything properly for you. Let me know how everything works out.

May 12, 2009
·
Like
0
·
Dislike
0

DKSilvia
First off, thanks a bunch! A lot of that made sense... But it comes up as #error. Here's what I have:

CASE( Extended_Warranty__c ,
"14 Months - Standard", DATE(YEAR(TODAY())+1,MONTH(TODAY())+2,DAY(Today())),
"28 Months - 2 Years", DATE(YEAR(TODAY())+2,MONTH(TODAY())+4,DAY(Today())),
"42 Months - 3 Years", DATE(YEAR(TODAY())+3,MONTH(TODAY())+6,DAY(Today())),
"56 Months - 4 Years", DATE(YEAR(TODAY())+4,MONTH(TODAY())+8,DAY(Today())),
"70 Months - 5 Years", DATE(YEAR(TODAY())+5,MONTH(TODAY())+10,DAY(Today())),
"84 Months - 6 Years", DATE(YEAR(TODAY())+7,MONTH(TODAY()),DAY(Today())),
NULL)

Which "." am I missing somewhere??

May 12, 2009
·
Like
0
·
Dislike
0

Slangan

Firstly, I would like to apologize as I gave you some wrong information in the last post.

If you use Today() in your expression, the warranty will re-calculate everyday and will never expire. This will obviously be a disaster for you. What you need to do instead is have a concrete start date. This can be a unique field titled Warranty Start Date, or it can be based on the ship date or close date of the opportunity. Once you decide what field will act as your measurement for the start of the Warranty, replace all of the Today() expressions with the field name (eg: Warranty_Start_Date__c).

Now onto your error.

Is it appearing in the field itself, or when you try to save the formula?

May 12, 2009
·
Like
0
·
Dislike
0

DKSilvia
In the calculated field itself. There is no error on saving the formula. (Thanks again!)

May 13, 2009
·
Like
0
·
Dislike
0

DKSilvia
Updated formula:

CASE( Extended_Warranty__c ,
"14 Months - Standard", DATE(YEAR( Ship_Date__c )+1,MONTH( Ship_Date__c )+2,DAY( Ship_Date__c )),
"28 Months - 2 Years", DATE(YEAR( Ship_Date__c )+2,MONTH( Ship_Date__c )+4,DAY( Ship_Date__c )),
"42 Months - 3 Years", DATE(YEAR( Ship_Date__c )+3,MONTH( Ship_Date__c )+6,DAY( Ship_Date__c )),
"56 Months - 4 Years", DATE(YEAR( Ship_Date__c )+4,MONTH( Ship_Date__c )+8,DAY( Ship_Date__c )),
NULL)

I ended up taking some options out because it maxed out the formula 5000 character limit... (dumb)

May 13, 2009
·
Like
0
·
Dislike
0

Slangan
There must be a problem with one of the fields that the formula is compiling information from. What type of field is your Ship Date code and field type?

May 13, 2009
·
Like
0
·
Dislike
0

DKSilvia
Actually it looks like the last change fixed it! Thank you VERY much for your help! I've been talking about making this formula for about a year, but didn't even know where to start! Now, not only do I have it working, you were helpful enough to explain WHAT each part was doing!

Cheers!
-Dan

May 13, 2009
·
Like
0
·
Dislike
0

Slangan
No problem. Thank-you for the feedback!

One more thing to keep in mind - an #Error message will show up in the formula field if you test it using a profile that does not have user permissions to view any of the fields used in generating its contents.

Shannon

May 14, 2009
·
Like
0
·
Dislike
0

DKSilvia
Ok...I lied slightly. Now anytime I have a ship date in Nov/Dec it returns an error, I believe because of the +2 in the months, and it gets confused and returns the error again. Does that make sense?

May 14, 2009
·
Like
0
·
Dislike
0

This was selected as the best answer

DKSilvia
Shannon,
   That looks right, but when I actually use it, it gives me the following error message: "Error: Field CloseDate does not exist. Check spelling." Is CloseDate a field you made? Should it be a field name that I already have in my database? Here's what I have (I did not add the other years back in, and my boss had me change the exact wording since last you saw it...)

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
      TEXT(YEAR( CloseDate )+1) & "-" &
      IF(MONTH(CloseDate)+2>12, TEXT(MONTH( CloseDate )+2-12), TEXT(MONTH(CloseDate)+2)) & "-" &
      TEXT(DAY( CloseDate )),
"2nd Year",
      TEXT(YEAR( CloseDate )+2) & "-" &
      IF(MONTH(CloseDate)+4>12, TEXT(MONTH( CloseDate )+4-12), TEXT(MONTH(CloseDate)+4)) & "-" &
      TEXT(DAY( CloseDate )),
"3rd Year",
     TEXT(YEAR( CloseDate )+3) & "-" &
      IF(MONTH(CloseDate)+6>12, TEXT(MONTH( CloseDate )+6-12), TEXT(MONTH(CloseDate)+6)) & "-" &
     (TEXT(DAY( CloseDate ))),
"4th Year",
     TEXT(YEAR( CloseDate )+4) & "-" &
     IF(MONTH(CloseDate)+8>12,TEXT(MONTH( CloseDate )+8-12), TEXT(MONTH(CloseDate)+8)) & "-" &
     TEXT(DAY( CloseDate )),
NULL))

I really appreciate all the time you're taking with this, and I'm sorry I'm being so much trouble!
-Dan

May 18, 2009
·
Like
0
·
Dislike
0

Slangan
My apologies. I thought I explained where CloseDate came from but I obviously wasn't very clear. I used CloseDate because it is a field available in our Org. You would use ShipDate or whatever date field you are using as a measurement to determine when the warranty formula starts.

Sorry for the confusion.

Let me know how it turns out.

Shannon

May 19, 2009
·
Like
0
·
Dislike
0

DKSilvia
Final Code:

DATEVALUE(CASE( Extended_Warranty__c ,
"14 Months - Standard",
TEXT(YEAR( Ship_Date__c )+1) & "-" &
IF(MONTH(Ship_Date__c)+2>12, TEXT(MONTH( Ship_Date__c)+2-12), TEXT(MONTH(Ship_Date__c)+2)) & "-" &
TEXT(DAY( Ship_Date__c)),
"2nd Year",
TEXT(YEAR( Ship_Date__c)+2) & "-" &
IF(MONTH(Ship_Date__c)+2>12, TEXT(MONTH( Ship_Date__c)+2-12), TEXT(MONTH(Ship_Date__c)+2)) & "-" &
TEXT(DAY( Ship_Date__c)),
"3rd Year",
TEXT(YEAR( Ship_Date__c)+3) & "-" &
IF(MONTH(Ship_Date__c)+2>12, TEXT(MONTH( Ship_Date__c)+2-12), TEXT(MONTH(Ship_Date__c)+2)) & "-" &
(TEXT(DAY( Ship_Date__c))),
"4th Year",
TEXT(YEAR( Ship_Date__c)+4) & "-" &
IF(MONTH(Ship_Date__c)+2>12,TEXT(MONTH( Ship_Date__c)+2-12), TEXT(MONTH(Ship_Date__c)+2)) & "-" &
TEXT(DAY( Ship_Date__c)),
NULL))

Everything is exactly as you said...once I actually READ it (sorry!). The only change besides the correct fields is a change in the adding...they decided not to overcomplicate it and just offer 14 months for the first one (to give a two month period the unit could be installed) then 12 months from that after...so it became all 2's instead of the increasing numbers in the months, but otherwise it is absolutely perfect!! Thank you SO much for all your help and patience!
-Dan

May 27, 2009
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Calculate Warranty Expiration Date from picklist

All Answers

You need to sign in to do that.