Toggle Side Panel
Close search

Activity › Forums › Salesforce® Discussions › Hours between two date time formula showing negative values in Salesforce

Tagged: , , ,

  • Salesforce® Discussions

    Hours between two date time formula showing negative values in Salesforce

    Posted by samuel john on December 14, 2016 at 2:52 PM

    I have written a formula to calculate the difference between two Date/Time fields,
    Please find the formula

    `ROUND(24*(
    (5*FLOOR((DATEVALUE(First_Response__c)-DATE(1996,01,01))/7) +
    MIN(5,
    MOD(DATEVALUE(First_Response__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/24*(MOD(First_Response__c-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
    ))
    -
    (5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) +
    MIN(5,
    MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) +
    MIN(1, 24/24*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
    ))
    ), 2)

    Here I have a case whose created date is 12/12/2016 2:16AM and Firstresponse date is 12/12/2016 10:54 AM now the case response hour formula field shows me the difference between the above as -15.37 .

    I tried to calculate the difference between two date Time for 24 hours not for a business hours, I only want to exclude weekends,

    The hour calculation should be from 12:00 AM today to 11:59 PM the next day.

    Please advise

    sushant replied 9 years, 7 months ago 2 Members · 1 Reply
  • 1 Reply

  • sushant

    Member
    February 2, 2017 at 3:20 PM

    Hi Samuel,

    Use this formula:

    ROUND(
    /* 24 * 5 days * weeks */
    24 * 5 * FLOOR((First_Response__c-CreatedDate)/7)
    /* max 24 * 5 days */
    + MIN( 120,
    /* days from the last week */
    24 * FLOOR( MOD((First_Response__c-CreatedDate)/7, 5) )
    /* hours difference */
    + MOD(First_Response__c-CreatedDate, 1)
    ),
    2)

    Thanks

Log In to reply.

Popular Salesforce Blogs

Popular Salesforce Videos