+ Start a Discussion
sgamcsgamc 

Subtract days from date

I have a formula field that calculates a date.

I have a picklist field with numbers (representing number of days).

 

I need a formula that lets me take the date - days and return a date.

 

This simple formula did not work:

Service_End_Date__c - Cancellation_Notice__c

as it returned this error on the Cancellation_Notice_c (picklist) field:

Error: Incorrect parameter for function -(). Expected number, date, datetime, received text.

 

SteveMo__cSteveMo__c

You need to convert your picklist text values into numeric values first, then you can do your formula.

 

You can do that using the CASE function like this:

 

 

CASE( Product_Probability__c, '0%',0.00, '25%', 0.25, '50%', 0.50, '75%', 0.75, '90%', 0.90, '100%', 1.00,NULL) * TotalPrice

 

 

 

Message Edited by Stevemo on 06-25-2009 10:47 AM
Message Edited by Stevemo on 06-25-2009 10:48 AM
BuellBuell

Service_End_Date__c - CASE( Cancelation_Notice__c,

picklist option 1, value 1,

picklist option 2, value 2,

picklist option 3, value 3,

... however many you have,

else result)

WPCMSWPCMS

With the new summer release of Salesforce you no longer need to use CASE with a pick list. Use the New Text(value) field. Try it out, it does wonders!

 

Before they introduced this new formula what I did to get around the whole pick list issue is create a hidden field. In your case it would be CancellationNoicePickVal as a formula and refernce the field in the formula. So you now have a field that is a text field with your Cancellation Notice info in it. Use that field in your formula.

 

But looking at your formula I am not sure it will work with a date field. I have not tried that. But if it doesn't work use my work around for other formulas.

SteveMo__cSteveMo__c
No matter which method you use, you still have to convert your picklist values from Text to Number in order to use them in your Date calculation.
sgamcsgamc

Thanks, I solved with the value function:

 

Service_End_Date__c - value(Cancellation_Notice__c )