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