This makes it much more complicated....
See attached and hopefully it works for you...
I added some helper columns to determine when the employee passes the 40 hour mark... These columns can be hidden if you wish...
so the formula for the first helper column would be
=IF(SUMPRODUCT(($A$2:$A$30=$A2)*ISNUMBER($D$2:D$30),$D$2:D$30)>=40,SUMPRODUCT(($A$2:$A$30=$A2)*ISNUMBER($D$2:D$30),$D$2:D$30)-40+(SUM($D2:$J2)-SUM($D2:D2)),0)
This is copied across 7 columns and down the list....
Then in L2:
=IF(A2="","",IF(COUNTIF($A$1:A2,A2)=1,MIN(IF(N2:T2>0,N2:T2)),MAX(N2:T2))-SUMIF($A$1:A1,A2,$L$1:L1))
which is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.
Then you can copy it down.
Bookmarks