0

I am trying to figure out what I am doing wrong in this Salesforce formula. I am trying to find the MIN between two dates.

IF(
  ISBLANK(Fulfillment_Date__c) && ISBLANK(Actual_Shipment_Date__c),
  NULL,
  IF(
    ISBLANK(Fulfillment_Date__c),
    Actual_Shipment_Date__c,
    IF(
      ISBLANK(Actual_Shipment_Date__c),
      Fulfillment_Date__c,
      NOW() - MIN(NOW() - Fulfillment_Date__c, NOW() - Actual_Shipment_Date__c)
    )
  )
)

I am getting the following error:

Error: Incorrect parameter type for operator '-'. Expected Number, DateTime, received Date

I don't know why as the Actual_Shipment_Date__c and Fulfillment_Date__c dates are Date data types.

Any help would be appreciated.

  • NOW() will return datetime so its better to get the Date using DATEVALUE() and then use it. – Rohit Mourya Jan 3 at 16:27
  • 2
    use TODAY() instead of NOW() , now gives date time type – sdandamud1 Jan 3 at 16:27
  • @RohitMourya I tried with 'DATEVALUE()' but am getting the following error: Error: Incorrect number of parameters for function DATEVALUE(). Expected 1, received 0 – Fadiddy Jan 3 at 16:42
  • 2
    DATEVALUE(NOW()) otherwise use TODAY() – Rohit Mourya Jan 3 at 16:43
  • @sdandamud1 when I user TODAY(), I get the following error: Error: Formula result is data type (Date), incompatible with expected data type (true or false). When I use NOW(), I get the following error: Error: Incorrect parameter type for operator '-'. Expected Number, DateTime, received Date – Fadiddy Jan 3 at 16:52
1

This error is expected because the operator - requires both the operand of same data type. The NOW() returns a datetime and other is your date field. Hence its giving you an error.

Solution would be to use either DATEVALUE(NOW()) or TODAY() (as suggested by sdandamud).

Also inner IF is not returning anything. If should always return True or False. It should be like this:

IF(
      AND (ISBLANK(Actual_Shipment_Date__c),
      Fulfillment_Date__c,
      TODAY() - MIN(TODAY() - Fulfillment_Date__c, TODAY() - Actual_Shipment_Date__c)), true, false
    )
  • When I use TODAY(), I get the following error: Error: Formula result is data type (Date), incompatible with expected data type (true or false). – Fadiddy Jan 3 at 16:51
  • Yes, that is expected. Its because your whole formula should return either TRUE or FALSE. Right now its not returning the expected value. – Rohit Mourya Jan 3 at 16:53
  • Oh ok, I see what you mean. So, can I nest this into another formula, or do I need to create separate formula fields and then reference one. For example, this is the formula for finding the lesser of two dates right? However, I have another formula filed for validating true and false conditions. Can I nest this into that formula? Here is a link to the other formula: link – Fadiddy Jan 3 at 17:01
  • Yes, you can nest it. – Rohit Mourya Jan 3 at 17:02
  • Rohit, I tried nesting it in my other formula....but same error. IF( OR( AND(TODAY() > Scheduled_Ship_Date__c, (Line_Status__c = "OPEN")), AND( OR( Actual_Shipment_Date__c > Scheduled_Ship_Date__c, Fulfillment_Date__c > Scheduled_Ship_Date__c ), (Line_Status__c = "CLOSED") ), AND( ISBLANK(Actual_Shipment_Date__c), Fulfillment_Date__c, NOW() - MIN(NOW() - Fulfillment_Date__c, NOW() - Actual_Shipment_Date__c) ), True, False ) ) – Fadiddy Jan 3 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.