Sla Hours Between Two Dates

  • Hello Wizards,


    I am having a hard time trying to calculate how many hours each user worked on a task(Item Number) based off a given report which is non-malleable.


    Each Item Number has a total time(SLA hours) which I was able to calculate with the below formula, excluding weekends, holidays and outside working schedule hours. Seems to work well, except when PM end time > start time. (It's probably bc of the US hours approach, maybe someone can propose something better? )


    Formula:

    '=(NETWORKDAYS.INTL(A2,B2,1,G$2:G$4)-1)*("17:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,1,G$2:G$4),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,G$2:G$4)*MOD(A2,1),"8:00","17:00")



    For each Item Number, every time the user changes the owner it's leaving a comment, which leaves a date, picked by the report. If it's moved, until the task it's being returned, the other user is accountable for the time.


    I would need a formula to substract from the whole business working time how much time every user spent on the task. (back n forth between them)


    Is this achievable?

    Thank you in advance!!

    Files

    • Example.xlsx

      (10.39 kB, downloaded 17 times, last: )