Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Calculate no of Work days between Opening & Closing Cases

  • 1.  Calculate no of Work days between Opening & Closing Cases

    Posted 14 days ago
    Hi All,

    I'm wondering if its possible to calculate the number of work days a case has been open for?  I can create a calculated field using =DIFFINDAYS(createdon, resolvedon) function but this doesn't take into account weekends.holidays, Business hours etc.

    Any ideas?  Thanks!

    ------------------------------
    Martin Whelan
    D365 CRM Analyst
    ------------------------------
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Calculate no of Work days between Opening & Closing Cases

    SILVER CONTRIBUTOR
    Posted 14 days ago
    Martin,

    We use the SLA feature to calculate this for us since we are able to feed Customer Service Calendars and Holiday Schedules into it.  We trigger the SLA when a case is opened, and mark it as successful when a case is resolved.  You can find these settings under the legacy Service Management settings menu.



    ------------------------------
    Jonathan Blackham
    Sales Operations Specialist
    Valin Corporation
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Calculate no of Work days between Opening & Closing Cases

    Posted 12 days ago
    Yes we use SLA's with with a Business Hours/Holidays schedule, but when I calculate the new field it doesn't  seem to allow for this, for example if I open a case on Friday and Close it on Monday it shows as 3 days whereas it should be 1....I am referencing the  Created On and Resolved On fields in the calculation, maybe I need to build in more logic in the Calculated field??

    Thanks...

    ------------------------------
    Martin Whelan
    CRM Support Analyst
    Health Business Services HBS
    Thomastown
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Calculate no of Work days between Opening & Closing Cases

    SILVER CONTRIBUTOR
    Posted 11 days ago
    Martin,

    It sounds like you are triggering your calculations strictly on the case record.  We pull timestamps from the related SLA record, which can be configured to only pay attention to business days.

    It sounds like you might be better served by either of these two different workflow/plugin extenders from Andrew Butenko's Ultimate Workflow Toolkit and Jason Lattimer's CRM Date/Time Workflow Utilities.  Your mileage may vary, but I have found them to be very useful.

    ------------------------------
    Jonathan Blackham
    Sales Operations Specialist
    Valin Corporation
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: Calculate no of Work days between Opening & Closing Cases

    Posted 11 days ago

    Hello Jonathan,

    Can you expand on "We trigger the SLA when a case is opened"? Are you generating a new SLA for each Case to track time?

    Thanks,



    ------------------------------
    Nathan Smyers
    Minitab, Inc.
    State College PA
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: Calculate no of Work days between Opening & Closing Cases

    SILVER CONTRIBUTOR
    Posted 11 days ago
    Hi @Nathan Smyers,

    I believe what Jonathan is referencing might be what we use in our system which is an 'SLA KPI Instance'. Each time a Case is created which has an SLA applied to it an SLA KPI Instance is created. This is basically CRM's little timestamps of the time rules your Case needs to be 1st responded in, resolved in etc.​



    Hope that helps.

    Jess


    ------------------------------
    Jessica Howes
    Microsoft Dynamics Specialist
    Opus
    Reigate
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role