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

  • 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, 6 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

Salesforce Admin Certification 2020 Questions Explained with References - Part II

Video in

How to prepare and pass Salesforce Admin Certification with Practice Exam Questions 2020 practice questions and answers for the Salesforce Administration Salesforce Certified Admin Exam Questions Salesforce Administrator Certification Exam…