"How long did it take to create this record?"

posted May 7, 2011, 11:15 PM by Ezra Kenigsberg   [ updated Jul 18, 2011, 11:31 AM ]

#DataTipOfTheDay 2011-05-08

Q: "How do I calculate how long it took to create a record?"

A: Cool question. Follow the three steps below to create a field called Time Elapsed: "New" Until "Save" (sec) that computes how many seconds elapse from the moment I click "New" until the moment I click "Save". This field tracks Call Duration (click "New" when answering the phone; click "Save" after completing the call).
  1. Create this custom formula field.
    Step 1) Type Formula
    Step 2) Name Days Since 1/1/1900: Created Date
    Step 2) Return Type Number
    Step 2) Decimal Places 13
    Step 3) Formula DATEVALUE(CreatedDate) - DATE(1900, 1, 1) + 
    VALUE(MID(TEXT(CreatedDate), 12, 2)) / 24 +
    VALUE(MID(TEXT(CreatedDate), 15, 2)) / 24 / 60 +
    VALUE(MID(TEXT(CreatedDate), 18, 2)) / 24 / 60 / 60
    Step 4) Visible to All Users
    Step 5) Add to Page Layouts None

  2. Create this custom number field.
    Step 1) Type Number
    Step 2) Name Days Since 1/1/1900: New Record
    Step 2) Integer Digits 5
    Step 2) Decimal Places 13
    Step 2) Default Value TODAY() - DATE(1900, 1, 1) + 
    VALUE(MID(TEXT(NOW()), 12, 2)) / 24 +
    VALUE(MID(TEXT(NOW()), 15, 2)) / 24 / 60 +
    VALUE(MID(TEXT(NOW()), 18, 2)) / 24 / 60 / 60
    Step 3) Visible to All Users
    Step 3) Read-Only for* No One
    Step 4) Add to Page Layouts* Add to whatever Page Layouts you wish

  3. Create this custom formula field.
    Step 1) Type Formula
    Step 2) Name Time Elapsed: "New" Until "Save" (sec)
    Step 2) Return Type Number
    Step 2) Decimal Places 0
    Step 3) Formula (Days_Since_1_1_1900_Created_Date__c - 
    Days_Since_1_1_1900_New_Record__c) *
    24 * 60 * 60
    Step 4) Visible to All Users
    Step 5) Add to Page Layouts Add to the same Page Layouts specified for the "Days Since 1/1/1900: New Record" field above
Caveats & Notes:
  • This logic will only work for records created after these fields are created.
  • These fields could help determine whether changing the UI helps users enter data faster.
  • The logic in these formulas should work correctly until about October 16, 2173. Feel free to write me an angry email if the formulas break after that.
Don't forget to wish your mother a happy Mother's Day! Thanks to Jason Lawrence for the question!

* Tweaked these settings after salesforce.com's Manu Erwin noticed a flaw in the logic. Still trying to figure out a slicker solution (suggestions welcome!)
Comments