Need help with adding time

[mrteater]

I need some additional assistance with a transpose formula. I have data on Sheet1 from an Avaya phone system report. On Sheet2 I have a formula that help me identify those who have lunches that last longer than 30 minutes and breaks that last longer than 36 minutes. I have a couple needs.

For the formula I need to take the total time worked (Row 22)
I need to subtract total time in Personal (Row 27)
I need to subtract total time in Personal (Row 31)
I need to subtract any time total in break over 30 minutes (Row 33)


First off I don't think my formula in row 39 is accurate as it is not only subtracting any time over 30 minutes for the break time. What I don't want to do is pay employees who exceed the allotted 30 minutes of break. For column B Kim spent 00:31.00 in break and 30 minutes is paid (2*15 minutes).

So the grand total for Kim in column B

In B39 total time worked 8:38:15
Minus Personal 0:11:06
Minus Lunch 0:31:41
Minus 0:01:00 for the 1 minute over break
For a grand total to equal a total of 7:56:28.

Then on Sheet 2 I would like to to identify those who have shifts less than 8 hours a day. I have tried to duplicate the formula on Sheet2 in E2 and E3 in H2 and H3 but I get a #Value error. The goal is for me to have a list of agent names that have time less than 8 hours (08:00:00) similar to the other two list. In this example it should show me the names of Jauquilise Curry and Cynthia Fleming. Can you assist?

[JeteMc]

For B39:G39 =B22-B27-B31-MAX(0,B33-30/1440)
For H3 and down: =IFERROR(INDEX(Sheet1!B$21:G$21,AGGREGATE(15,6,(COLUMN(Sheet1!B$21:G$21)-COLUMN(Sheet1!A$21))/(Sheet1!B$39:G$39<8/24),ROWS(H$3:H3))),"")
For I3 and down: =IF(H3="","",SUMIFS(Sheet1!B$39:G$39,Sheet1!B$21:G$21,H3))
Let us know if you have any questions.