Skip to main content
Topics#Data ManagementQuestion de Brent Downey
Brent Downey (Pushpay) a posé une question dans #Data Management
22 mai 2017, 20:23

Formula that calculates the most recent date/time (between two date/time fields).

I need a formula that will evaludate two date/time fields and reutnr the most recent/latest date/time. I found the following knowledge article, which provided a formula:

 

https://help.salesforce.com/articleView?id=000213464&type=1

 

The problem is that this formula is returning NOW() if both fields are blank, or one is blank and the other is in the past. I tried to update this formula to return NULL if both fields are NULL, but it's not working as I expcted. Here's the modofied formula:

 

 

IF(ISBLANK(DateTimeField1__c) && ISBLANK(DateTimeField2__c), NULL,
NOW() - MIN(
IF(ISNULL(NOW()-DateTimeField1__c),0,NOW()-DateTimeField1__c),
IF(ISNULL(NOW()-DateTimeField2__c ),0,NOW()-DateTimeField2__c )
))

 

I can't seem to get the right syntax to get a seccessful syntax pass when trying to update further. Any suggestions?
13 réponses
  1. Deepak K Anand (‎‎‎‎‎‎Zoom Communications, Inc.)
    22 mai 2017, 20:34

    I guess this should do it - 

    IF(
        AND(
            ISBLANK(DateTimeField1__c),
            ISBLANK(DateTimeField2__c)
        ),
        NULL,
        NOW() - MIN(
            NOW() - BLANKVALUE(DateTimeField1__c, DATETIMEVALUE("1900-01-01 12:00:00")),
            NOW() - BLANKVALUE(DateTimeField2__c, DATETIMEVALUE("1900-01-01 12:00:00"))
        )
    )
Rédiger une réponse...
0/9000