Work time calcultion v2

[Pofski]

Hi everybody,


This is a followup of a previous question that i asked a couple of months ago ( http://chandoo.org/forum/threads/work-time-calculation.12074/#post-70952 )

i have added the resulting file after the thread discussion, because i seem to have run into a weird situation, where the calculation goes way off.

If you take Cati for example, she seems to have way more hours in the calculation then she ever had assigned to her.
Seeing as i am still wrestling with really understanding the formula used here (courtesy of Sajan) i was wondering if anybody could help me out with a game of " Spot the problem"

Thanks in advance

 

[Jake Collins]

Dear Pofski

The problem is that the formula doesn't take any account of the translator.

=SUM((IFERROR(Estimate/MMULT((TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))>=StartDate)*(TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))<=DueDate), ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))^0),0) * MMULT((TRANSPOSE(WORKDAY(M15,ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))))>=StartDate)*(TRANSPOSE(WORKDAY(M15,ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))))<=DueDate), ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))^0))*(Translator=$L$2))
replacing the last closing bracket with *(Translator=$L$2)) where L2 is storing the name of the translator name you wish to determine the hours for should do the trick.