formula calculate overtime and delaying to worker for month

[abdelfattah]

=hello
Sheet Axel is designed to come and leave workers for days of the month and workers

Sheet excel The most important point is to calculate the number of overtime and the number of delay hours for each worker throughout the month

At the end of the sheet there are two boxes

The first digit is the calculation of overtime is d3 : how the equation is made, knowing that the official exit time is 6.00 pm

And also the second square: the number of hours of delay is d2 throughout the month, knowing that the start of work is 7.30 am

Knowing also: Friday and Saturday are vacation


Is there a specific way in which I can calculate the number of additional hours and delay during the month?

Taking into account in the case of an employee leaving before the work date

the result bq9 ,br9
bq 9= total column (dissmis)
br 9= total column (presentce)

I wish I could find a solution to this problem, and I would be grateful to you

شيت حضور وانصراف.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR
1
2		start time		7:30 PM
3		end time		6:00 PM
4
5
6			SAT		SUN		MON		TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED
7	item	name	05/25/2020		05/26/2020		05/27/2020		05/28/2020		05/29/2020		05/30/2020		05/31/2020		06/01/2020		06/02/2020		06/03/2020		06/04/2020		06/05/2020		06/06/2020		06/07/2020		06/08/2020		06/09/2020		06/10/2020		06/11/2020		06/12/2020		06/13/2020		06/14/2020		06/15/2020		06/16/2020		06/17/2020		06/18/2020		06/19/2020		06/20/2020		06/21/2020		06/22/2020		06/23/2020		06/24/2020		06/25/2020		06/26/2020		OVERTIME	DELAYING
8			PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS	PRESENTCE	DISSMIS
9	1	JOHN	-	-	8:30 AM	7:30 PM	7:30 AM	5:30 PM	8:30 AM	7:30 PM	9:30 AM	6:00 PM	7:30 AM	6:00 AM	-	-	-	-	8:30 AM	7:30 PM	7:30 AM	5:30 PM	8:30 AM	7:30 PM	9:30 AM	6:00 PM	7:30 AM	6:00 AM					8:30 AM	7:30 PM	7:30 AM	5:30 PM	8:30 AM	7:30 PM	9:30 AM	6:00 PM	7:30 AM	6:00 AM					8:30 AM	7:30 PM	7:30 AM	5:30 PM	8:30 AM	7:30 PM	9:30 AM	6:00 PM	7:30 AM	6:00 AM					8:30 AM	7:30 PM	7:30 AM	5:30 PM	8:30 AM	7:30 PM	9:30 AM	6:00 PM
10	2	SMITH
Sheet1

 

[abdelfattah]

I would like to add information if the departure time in column (dissmis) is less than the legal time in d3 , then the difference is calculated as a delay and it is added to the delay in br9 (delaying).

 

[abdelfattah]

up

 

[abdelfattah]

anybody help