Tracking Quarterly Total Hours

[RandyD123]

I have a sheet with two tabs. Both seen below: What I need to do is calculate total hours each employee Shift Trades Off. The first sheet does that for each entry. What I need to do is show total hours per quarter on the Data Tab. So for example "Employee #1 puts in a lot of trades. As long as they don't give away more than 20% of there total hours per quarter they are fine. But if they go over I need to have that flagged as shown in my conditional formatting. (Those over hours are just showing what will happen if they go over the allotted amount. I'm not sure if I use VBA or a Formula on the Data Tab. I hope I've given enough info. Thank in advance for any help.

Book1 - Copy.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		STW (Person Working)	STO (Person Not Working)	Date Shift Trade Will Occur	Time Start	Time End	Total Hours Traded Off	Person Who Made This Entry	Date Person Made This Entry
4		Name #08	Name #03	01/17/23	0400	0800	4.0
5		Name #03	Name #01	02/27/23	1200	1630	4.5
6		Name #04	Name #03	03/15/23	1300	1830	5.5
7		Name #06	Name #01	04/22/23	0430	0730	3.0
8		Name #02	Name #04	04/28/23	0730	2000	12.5
9		Name #08	Name #04	05/02/23	1130	1730	6.0
10		Name #05	Name #02	05/14/23	0330	1200	8.5
11		Name #01	Name #03	06/12/23	1800	2000	2.0
12		Name #07	Name #01	07/04/23	1700	2400	7.0
13		Name #71	Name #02	07/30/23	0330	0730	4.0
14		Name #08	Name #02	08/02/23	0330	1200	8.5
15		Name #110	Name #04	08/17/23	1300	1800	5.0
16		Name #119	Name #02	08/18/23	1300	1800	5.0
17		Name #06	Name #04	08/19/23	0330	1200	8.5
18		Name #22	Name #01	09/22/23	0800	1200	4.0
19		Name #114	Name #03	09/27/23	1200	1800	6.0
20							0.0
Shift Trades

Cell Formulas
Range	Formula
G4:G20	G4	=(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24

Cells with Data Validation
Cell	Allow	Criteria
B4:C20	List	=Data!$B$4:$B$149

Book1 - Copy.xlsm
	A	B	C	D	E	F	G	H	I
1
2				20%
3		Names	Hours / PP	Hours / Quarter	1/1/2023	4/1/2023	7/1/2023	10/1/2023
4		Name #01	40	52	4.5	3	11
5		Name #02	80	104		8.5	12.5	5
6		Name #03	80	104	7.5	2	4
7		Name #04	80	104		18.5	8.5
8		Name #05	40	52	4.5
9		Name #06	40	52
10		Name #07	40	52
11		Name #08	40	52	53
12		Name #09	40	52		54
13		Name #10	80	104			105
14		Name #11	40	52				53
15		Name #12	80	104
16		Name #13	80	104
Data

Cell Formulas
Range	Formula
D4:D16	D4	=SUM(C4 * 6.5)*($D$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:H149	Expression	=SUM(H4>D4)	text	NO
G4:G149	Expression	=SUM(G4>D4)	text	NO
F4:F149	Expression	=SUM(F4>D4)	text	NO
E4:E149	Expression	=SUM(E4>D4)	text	NO

 

[RandyD123]

Just wanted to check in and see if anyone could help with this. Not sure if I explained it correctly or not. So the Data sheet needs to add up all the shift trade OFF's each employee takes per quarter. Once that quarter is over, then their hours should track in the next quarter column. Just not sure how to tie one sheet to the other. I'm assuming a lot of VBA or a formula that is beyond me. Thanks again for taking a look.