function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
markcejonesmarkcejones 

Calculating Time - Difference Between 2 Custom Date/Time Fields

Hi,

 

I have 2 custom Date/Time fields for example. 

 

i) L1 = date/time when case is assigned to Level 1 Support

ii) L2 = date/time when case is assigned to Level 2 Support

 

I wanted to calculate the time the Case has spent in L1 Support, for example.  I came up with the following logic:

 

 

IF(IsNull(L2__c), (ClosedDate - L1__c), (L2__c - L1__c)

 

Basically, if L2 is null (ie: Case wasn't assigned to L2), then the time spent in L1 is the ClosedDate - L1.   If L2 is not null (ie: Case was assigned to L2), then the time spent in L1 = L2 - L1. 

 

This is great, but only if the Case is Closed.  

 

What if the Case is still Open, in L1 Support?

 

I've tried several IF(AND(IsNull...but I am receiving missing ')' and expecting boolean, etc.  Is there a way to do nested IFs?

 

Thanks for any tips.

 

-Mark

 

 

 

 

ShahafShahaf
 Hi Mark What you are looking for is a trigger that will update the status of all your cases and log their up to date time while they are open. unfortunately even in this case you will be missing a key metric which is the actual time spent on case; what you have now is the time from opening until closing / stage change. in this system you can only report general time to escalation and generals closure rates with stage breakdown but you cant really drill down elements that causes duration in each stage, which in turn doesn’t supply you the right metrics and reports required for increasing efficiency/productivity/quality of service. 

Take a look at "WorkIt! case time tracking solution" on the appexchange, I believe it provides the metrics you require and more.

https://sites.secure.force.com/appexchange/listingDetail?listingId=a0N30000001gZ0hEAE