Print this page

How to work out the number of hours since the last Public Case Comment?

Knowledge Article Number 000181626
Description
How to calculate the Date and Time between the last public case comment and current Date/Time.
Resolution To achieve such functionality the Setup will require the following:
  1. One custom Workflow Rule with a field update action.
  2. One hidden custom Date/Time field which will be updated and stamped with the date and time when the last comment has been added.
  3. One hidden custom formula Date/Time Type field which will retrieve the current date and time.
  4. One hidden custom formula Number type field which will calculate between the current date/time and the date time of the last comment added on the case (The value will be retrieved in "numbers" as the date/time values are stored in the Salesforce DataBase as numbers)
  5. One custom formula field visible on the case layout, which converts the value number into a Date/Time value.

Follow the below mentioned steps to fulfill the requirement: 
 
  1. Create a new custom Date/time field on the Case object which is hidden from the page layout, named i.e " Last Comment Added"
  2. Create New Workflow Rule.
    • Object:Case Comments
    • Evaluation Criteria: When a record is created, or when a record is edited and did not previously meet the rule criteria
    • Rule Criteria: Case Comment: Created Date - EQUALS - TODAY (For Public comments only add also : Case Comment:Published - Equals - True)
    • Create a Workflow Actions: "Immediate Workflow Actions"which will update the field "Last Comment Added"
    • Use a Formula Value of : NOW()
    • Save and activate the Workflow Rule.
  3. Create a new custom Formula field with the formula return type "Date/Time" on the Case object named i.e "Current Date and Time", which is also hidden from the page layout.
    • Use the following function: NOW()
  4. Create a new custom Formula field with the formula return type "Number" on the Case object named i.e "Retreive DateTime value Number", which is also hidden from the Case object page layout.
    • Use the following function: NOW() - Last_Comment_Added_c
  5. Create a new custom Formula field with the formula return type "TEXT" on the Case object named i.e "Time since last comment", which will need to be visible on the Case object page layout.
    • Use the following function :
IF ( Retreive_DateTime_value_Number__c > 0,
TEXT(FLOOR( Retreive_DateTime_value_Number__c)) & " days " &
TEXT( FLOOR( 24 * ( Retreive_DateTime_value_Number__c - FLOOR(Retreive_DateTime_value_Number__c) ))) & " hours" &
TEXT( ROUND(60 * (ROUND( 24 * ( Retreive_DateTime_value_Number__c - FLOOR(Retreive_DateTime_value_Number__c) ),8) - FLOOR(ROUND( 24 * ( Retreive_DateTime_value_Number__c - FLOOR(Retreive_DateTime_value_Number__c) ),8)) ),0)) & " mn ", "")




promote demote