Knowledge Management

Get Involved. Join the Conversation.

Topic

    Don Pillsbury
    Helpful Formula - Business Days Calculation, Date_Diff
    Topic posted May 11, 2017 by Don PillsburyGreen Ribbon: 100+ Points, last edited May 11, 2017 
    436 Views, 12 Comments
    Title:
    Helpful Formula - Business Days Calculation, Date_Diff
    Content:

    I was disappointed in the limited capability of the rel_date_diff function. (It can’t be nested in other expressions.) Instead I came up with this formula for calculating the number of business days between two dates that I’ve not seen anyone else in the community reference.

     

    ((DATE_DIFF(date2,date1)/86400) + 1) - ((to_number(date_format(date2,'WW')) - to_number(date_format(date1,'WW')))* 2) - IF(Date_format(date1,'DAY') = 'Sunday',1,0) - IF(Date_format(date2,'DAY') = 'Saturday',1,0)

     

    Happy Reporting,

     

    Subject edited by Danette Beal for clarification

    Version:
    RightNow
    Code Snippet:

    Comment

     

    • Mike Kramer

      I'm disappointed in that function as well.  I was just trying to implement it and gave up.  I'll try your calculation to see of I can get better results.  Appreciate the work you put into this one. 

    • Danette Beal

      This is great!!! I'll have to try it out on some of the reports that I've really struggled with the date_diff functionality. 

      Keep the good suggestions coming!!!

      Danette, Community Manager

    • Barrilito van Dijk

      Hi Don,

      Thank you for sharing your thoughts, always good to see people share some ideas!

      Regards

    • Simon Kilgarriff

      Hi Don,

      Nice formula.  Calculations in business days/hours are always tricky.   I guess that formula doesn't take into account public holidays though which could be an issue for some folks who need those also excluded from the calculation.  I would assume the rel_date_diff would take into account holidays as it is based on response requirements and slas.

      Simon

    • Don Pillsbury

      I just made a discovery that I wanted to share and add to this post.

      Even better then the above formula is using the Incident Performance table. Turns out it has the data I wanted (but didn't know existed when I resorted to creating this formula).

      Details about the different intervals in the table can be found in this answer: https://cx.rightnow.com/app/answers/detail/a_id/2240/

      Happy reporting,

    • Sebastiaan Draaisma

      Always liked this formula! (voted) :-)
      I created a sample report based on this formula that takes public holidays into account. I will attach it here for others to explore

    • Ineke Clewer

      Has anyone found a way to do date_add to only add business days? (eg on a friday add one day gives the monday?)

      I assume you should be able to do something using similar logic to the above, but I can't quite get my head around the above yet!

    • Randy Riess

      I apologize for necro-ing this post, but it's the most helpful formula I've used.  I'm running into issues since the new year began - the first week of the year is showing incidents starting at 100+ days old.  Is there an adjustment that can be made in this formula?  Thank you.

    • Sebastiaan Draaisma

      I recently did something for Ashok with rel_date_diff and a custom script. That way you have all your holidays automatically excluded.

    • Randy Riess

      That's great Sebastiaan thank you.  I also use this formula with sysdate and incidents.created date to see the current open time of an incident.  Can this be adjusted for that purpose as well? That's where I seemed to hit a snag with this formula.  By using sysdate and incidents.created date the open time for specific incidents from the previous year were exponentially higher.  It appears now it's corrected itself since it is now looking back to weeks of the same year, however for those incidents prior I'd like to see if an adjustment could be made.

      It's also worth mentioning I've attempted to use the relative date difference for this to get incident open time, however our SLA's are setup for 11 hour periods and cannot be changed.  We'd like it to be 24 hours, but can't seem to find a solution.  Thanks again for your help.