1

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.

Improve this question
5
  • NOW() will return datetime so its better to get the Date using DATEVALUE() and then use it.
    – Rohit Mourya
    Jan 3, 2020 at 16:27
  • 2
    use TODAY() instead of NOW() , now gives date time type
    – sdandamud1
    Jan 3, 2020 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
    – PowerUp
    Jan 3, 2020 at 16:42
  • 2
    DATEVALUE(NOW()) otherwise use TODAY()
    – Rohit Mourya
    Jan 3, 2020 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
    – PowerUp
    Jan 3, 2020 at 16:52

1 Answer 1

Reset to default
2

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
    )
Improve this answer
10
  • When I use TODAY(), I get the following error: Error: Formula result is data type (Date), incompatible with expected data type (true or false).
    – PowerUp
    Jan 3, 2020 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, 2020 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
    – PowerUp
    Jan 3, 2020 at 17:01
  • Yes, you can nest it.
    – Rohit Mourya
    Jan 3, 2020 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 ) )
    – PowerUp
    Jan 3, 2020 at 17:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .