Forum Discussion

naomi_j's avatar
naomi_j
Frequent Visitor
6 years ago

Calculate working hours between two dates

I'm trying to calculate the number of working hours/minutes between two dates (ie. excluding non-working hours and weekends).  

 

This is the basic format - and I'm trying to find out how long it takes for an issue to get resolved, in working hours/minutes.  Can anyone help, please?

 

Issue idCreatedResolved
126540003/03/2020 13:5403/03/2020 14:21
126525603/03/2020 12:5403/03/2020 13:45
126380103/03/2020 09:0903/03/2020 09:11
126378903/03/2020 08:4203/03/2020 10:50
126232102/03/2020 17:1203/03/2020 09:18
126198502/03/2020 11:5402/03/2020 11:57
125873528/02/2020 22:2702/03/2020 16:53
125868728/02/2020 16:2603/03/2020 10:12
125863828/02/2020 14:5402/03/2020 16:00
Need Help

11 Replies

    • naomi_j's avatar
      naomi_j
      Frequent Visitor
      6 years ago

      Thanks Greg - it seems the Net Work Days option should be close to what I want.  Although I will have the same issue as the other user who commented in the thread (start and finish times potentially occurring out of work hours).  Was there a solution for avoiding the negative numbers?

       

      Thanks again!

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Community Support
        6 years ago

        naomi_j 

         

        You may calculate working hours/minutes since Mon, Feb 24, 2020.

        DIFF ( <Base>, <Resolved> ) - DIFF ( <Base>, <Created> )

         

    • naomi_j's avatar
      naomi_j
      Frequent Visitor
      6 years ago

      Greg_Deckler - I tried your net workdays option and it worked (thankfully no negative numbers output)!  Thanks!  

       

      I changed the output to hours, which works better for me - although this seems to round off rather than offering part hours.  Do you know if there's a way to have the output in part-hours (eg. 1.25 for 1hr12mins instead of 1)?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago
        naomi_j - Not entirely certain exactly what your code is but perhaps try increasing the number of decimals?
  • jolewis's avatar
    jolewis
    Regular Visitor
    5 years ago

    Hi,

     

    Looking at calculate minutes between a ticket was created and closed.

     

    The measure needs to consider the following:

    - Should ignore public holidays (Have a table called public holidays in which the date column has the days I have as public holidays)

    - Should consider Mon to Friday

    - Business Hours 7 am to 5 pm

    - If completed is greater than created then should take it as 0

     

    Step created at AEDT           Step Completed at AEDT

    15/6/21 11:32:24 am             16/6/21 2:22:46 pm

     

    Any help will be appreciated