+ Start a Discussion
Sivan NaorSivan Naor 

Date format in formula

Hi all,

I am trying to create a formula that will calculate a certain amount ,according to specific dates (NPSP version). It seems like I have a problem with the date format, since this is the error message I get:
"Error: Incorrect parameter type for operator '>='. Expected Date, received Number"

This is the formula:
IF(npe01__Payment_Date__c >= 2008-01-01 && npe01__Payment_Date__c < 2011-01-01, npe01__Payment_Amount__c*1.25+3.21,  IF(npe01__Payment_Date__c > 2011-01-01, npe01__Payment_Amount__c*1.25, npe01__Payment_Amount__c) )

The Payment Amount date is a date field. Does anyone have any idea regards to how should I type the date correctly?

Any help much appreciated,

Sivan
Best Answer chosen by Sivan Naor
JustAGirlyGeekJustAGirlyGeek
Hopefully this will give you the desired result:
 
IF(Linked_to_valid_gift_aid_declaration__c = TRUE,
IF(npe01__Payment_Date__c >= DATEVALUE("2008-01-01") && (npe01__Payment_Date__c < DATEVALUE("2011-01-01")), (npe01__Payment_Amount__c*0.25)+3.21, 
IF(npe01__Payment_Date__c > DATEVALUE("2011-01-01"), (npe01__Payment_Amount__c*0.25), 0)),0)

 

All Answers

JustAGirlyGeekJustAGirlyGeek
Give this formula a shot:

IF(npe01__Payment_Date__c >= DATEVALUE("2008-01-01") && npe01__Payment_Date__c < DATEVALUE("2011-01-01"), npe01__Payment_Amount__c*1.25+3.21,  IF(npe01__Payment_Date__c > DATEVALUE("2011-01-01"), npe01__Payment_Amount__c*1.25, npe01__Payment_Amount__c) )
Sivan NaorSivan Naor
Perfect! It worked beautifuly, only then I remembered I have an additional condition:

IF(Linked_to_valid_gift_aid_declaration__c = TRUE,
IF((npe01__Payment_Date__c >= DATEVALUE("2008-01-01") && (npe01__Payment_Date__c < DATEVALUE("2011-01-01")), (npe01__Payment_Amount__c*0.25)+3.21,
IF((npe01__Payment_Date__c > DATEVALUE("2011-01-01")), npe01__Payment_Amount__c*0.25, 
0 ),0),0))


Now I keep getting errors that don't make any sence. For this formula it's Error: Syntax error. Missing ')'​, but if I add the ) in the marked place, the error is Error: Syntax error. Extra ')'

Any thoughts on that?

Thanks so much,

Sivan
JustAGirlyGeekJustAGirlyGeek
Hopefully this will give you the desired result:
 
IF(Linked_to_valid_gift_aid_declaration__c = TRUE,
IF(npe01__Payment_Date__c >= DATEVALUE("2008-01-01") && (npe01__Payment_Date__c < DATEVALUE("2011-01-01")), (npe01__Payment_Amount__c*0.25)+3.21, 
IF(npe01__Payment_Date__c > DATEVALUE("2011-01-01"), (npe01__Payment_Amount__c*0.25), 0)),0)

 
This was selected as the best answer
Sivan NaorSivan Naor
Awsome! Thank you so much for your help.