Sla Hours Between Two Dates

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!