formulas Help in determining the number of working hours and extracting additional hours from them

[abdelfattah]

i have file is equipped table to determine the number of working hours (two terms) and extract them additional hours
I would like to help fill the cells with the appropriate formula with the following notes
1. Official working hours are 8 hours and what has increased is the extra time, and numbers entered at the time of entry and exit system for the two periods are 24 hours
2- Any Friday in which the time is completely extra
3. Vertical cells added in another table and is a names of days, may help in the equation to determine if the calculated full day in the case of a cell "Today the name of" Gomaa or otherwise
(And this column in each month will be different based on the order of days in each month and will be arranged to change every time)
4. There is in another cell total hours Het extra time for the entire

Work Time Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2				Time Card
3
4			26 XX.2020 To 25 XX.2020
5
6		Morninig		After Noon
7		Log in	Log Out	Log in	Log Out	Hrs worked		OverTime Hours		Day Name
8	26	7:57	12:00	12:30	16:02					Tuesday
9	27	7:50	11:59	12:38	16:08					Wednesday
10	28	7:54	12:02	12:49	17:45					Thursday
11	29	10:56	11:56	12:51	15:44					Friday
12	30	7:40	12:00	12:30	15:30					Saturday
13	31	8:00	12:00	12:30	16:06					Sunday
14
15	1	7:57	12:00	12:30	16:02					Monday
16	2	7:50	11:59	12:38	16:08					Tuesday
17	3	7:54	12:02	12:49	17:45					Wednesday
18	4	7:53	11:54	12:52	18:32					Thursday
19	5	13:00	15:00							Friday
20	6	7:55	12:01	12:32	18:37					Saturday
21	7	7:56	11:56	12:51	18:44					Sunday
22	8	8:02	11:58	12:50	18:04					Monday
23	9	8:08	11:59	12:26	18:02					Tuesday
24	10	7:00	12:09	12:50	16:31					Wednesday
25	11	7:55	11:59	12:50	17:01					Thursday
26	12	7:00	12:09	12:50	16:31					Friday
27	13	7:47	12:02	14:39	16:31					Saturday
28	14	7:29	12:03	14:01	17:52					Sunday
29	15	6:56	12:19	14:39	17:07					Monday
30	16	7:55	11:59	12:50	17:01					Tuesday
31	17	7:00	12:09	12:50	16:31					Wednesday
32	18	7:47	12:02	14:39	16:31					Thursday
33	19	7:29	12:03	14:01	17:52					Friday
34	20	6:56	12:19	14:39	17:07					Saturday
35	21	7:29	12:03	14:01	17:52					Sunday
36	22	6:56	12:19	14:39	17:07					Monday
37	23	7:55	11:59	12:50	17:01					Tuesday
38	24	7:00	12:09	12:50	16:31					Wednesday
39	25	7:47	12:02	14:39	16:31					Thursday
40
41
42		Total OverTime (Hours)
43
44
45
26_XX 2020 To 26_XX 2020

 

[CA_Punit]

can you help me out with the Third Condition.. What do you mean..

 

[abdelfattah]

what about you know about condition 3 to I explain for you differently way

 

[Toadstool]

Hi AbdelFattah,

I hope you are OK if I make changes to the format of your sheet. The first thing to be aware of is that these calculations will only work if nobody works past midnight (meaning they do not Log In one day and Log Out the next day).

Changes:

1. Removed empty row 14.
2. Cell A8 changed to contain the first date of the Time Card. Other dates are then calculated and the range in cell C4 is calculated. The calculated dates in A9 to A38 must be less than the A8 starting date plus 1 month.
3. I am assuming that "Any Friday in which the time is completely extra" means any hours worked on Friday are all overtime hours.
4. The day of the week is calculated in column J. It is not needed for the calculations (as WORKDAY gives that) but you may want to use Conditional Format to highlight the row.
5. I do not understand point 3 but I can guess that these are Public Holidays so are treated the same as Friday, every hour is overtime? If so I have put a table in columns L and M where you can identify those holiday dates.

Here is my modified sheet (please note the time formats are custom [h]:mm so that totals can go over 24 hours.

AbdelFattah.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1												Holidays	Date
2				Time Card								Labour Day	01-May-20
3												Eid al-Fitr	24-May-20
4		26 May.2020 to 25 Jun.2020										Eid al-Fitr	25-May-20
5												Test data	31-May-20
6		Morninig		After Noon								National Day	23-Jul-20
7		Log in	Log Out	Log in	Log Out	Hrs worked		OverTime Hours		Day Name
8	26-May	7:57	12:00	12:30	16:02	7:35				Tuesday
9	27-May	7:50	11:59	12:38	16:08	7:39				Wednesday
10	28-May	7:54	12:02	12:49	17:45	9:04		1:04		Thursday
11	29-May	10:56	11:56	12:51	15:44	3:53		3:53		Friday
12	30-May	7:40	12:00	12:30	15:30	7:20				Saturday
13	31-May	8:00	12:00	12:30	16:06	7:36		7:36		Sunday
14	1-Jun	7:57	12:00	12:30	16:02	7:35				Monday
15	2-Jun	7:50	11:59	12:38	16:08	7:39				Tuesday
16	3-Jun	7:54	12:02	12:49	17:45	9:04		1:04		Wednesday
17	4-Jun	7:53	11:54	12:52	18:32	9:41		1:41		Thursday
18	5-Jun	13:00	15:00			2:00		2:00		Friday
19	6-Jun	7:55	12:01	12:32	18:37	10:11		2:11		Saturday
20	7-Jun	7:56	11:56	12:51	18:44	9:53		1:53		Sunday
21	8-Jun	8:02	11:58	12:50	18:04	9:10		1:10		Monday
22	9-Jun	8:08	11:59	12:26	18:02	9:27		1:27		Tuesday
23	10-Jun	7:00	12:09	12:50	16:31	8:50		0:50		Wednesday
24	11-Jun	7:55	11:59	12:50	17:01	8:15		0:15		Thursday
25	12-Jun	7:00	12:09	12:50	16:31	8:50		8:50		Friday
26	13-Jun	7:47	12:02	14:39	16:31	6:07				Saturday
27	14-Jun	7:29	12:03	14:01	17:52	8:25		0:25		Sunday
28	15-Jun	6:56	12:19	14:39	17:07	7:51				Monday
29	16-Jun	7:55	11:59	12:50	17:01	8:15		0:15		Tuesday
30	17-Jun	7:00	12:09	12:50	16:31	8:50		0:50		Wednesday
31	18-Jun	7:47	12:02	14:39	16:31	6:07				Thursday
32	19-Jun	7:29	12:03	14:01	17:52	8:25		8:25		Friday
33	20-Jun	6:56	12:19	14:39	17:07	7:51				Saturday
34	21-Jun	7:29	12:03	14:01	17:52	8:25		0:25		Sunday
35	22-Jun	6:56	12:19	14:39	17:07	7:51				Monday
36	23-Jun	7:55	11:59	12:50	17:01	8:15		0:15		Tuesday
37	24-Jun	7:00	12:09	12:50	16:31	8:50		0:50		Wednesday
38	25-Jun	7:47	12:02	14:39	16:31	6:07				Thursday
39
40
41		Totals			Hours	245:01	Overtime	45:19
Sheet1

Cell Formulas
Range	Formula
B4	B4	=TEXT(MIN($A$8:$A$38),"dd mmm.yyyy")&" to "&TEXT(MAX($A$8:$A$38),"dd mmm.yyyy")
A9:A38	A9	=IF(A8="","",IF(A8+1=EDATE($A$8,1),"",A8+1))
F8:F38	F8	=IF(A8="","",(C8-B8)+(E8-D8))
H8:H38	H8	=IF(A8="","",IF(OR(WEEKDAY(A8,16)=7,NOT(ISNA(MATCH(A8,$M$2:$M$99,0)))),F8,IF(F8>TIME(8,0,0),F8-TIME(8,0,0),"")))
J8:J38	J8	=TEXT(A8,"dddd")
F41,H41	F41	=SUM(F8:F38)

 

[abdelfattah]

i need fix col a,h and d2 format of date not right as my picture

Work Time Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J
3
4				26 Jan.1900 to 24 Feb.1900
5
6		Morninig		After Noon
7		Log in	Log Out	Log in	Log Out	Hrs worked		OverTime Hours		Day Name
8	26	7:57	12:00	12:30	16:02	07:35				Thursday
9	27	7:50	11:59	12:38	16:08	07:39		0.32		Friday
10	28	7:54	12:02	12:49	17:45	09:04		0.04		Saturday
11	29	10:56	11:56	12:51	15:44	03:53				Sunday
12	30	7:40	12:00	12:30	15:30	07:20				Monday
13	31	8:00	12:00	12:30	16:06	07:36				Tuesday
14	32	7:57	12:00	12:30	16:02	07:35				Wednesday
15	33	7:50	11:59	12:38	16:08	07:39				Thursday
16	34	7:54	12:02	12:49	17:45	09:04		0.38		Friday
17	35	7:53	11:54	12:52	18:32	09:41		0.07		Saturday
18	36	13:00	15:00			02:00				Sunday
19	37	7:55	12:01	12:32	18:37	10:11		0.09		Monday
20	38	7:56	11:56	12:51	18:44	09:53		0.08		Tuesday
21	39	8:02	11:58	12:50	18:04	09:10		0.05		Wednesday
22	40	8:08	11:59	12:26	18:02	09:27		0.06		Thursday
23	41	7:00	12:09	12:50	16:31	08:50		0.37		Friday
24	42	7:55	11:59	12:50	17:01	08:15		0.01		Saturday
25	43	7:00	12:09	12:50	16:31	08:50		0.03		Sunday
26	44	7:47	12:02	14:39	16:31	06:07				Monday
27	45	7:29	12:03	14:01	17:52	08:25		0.02		Tuesday
28	46	6:56	12:19	14:39	17:07	07:51				Wednesday
29	47	7:55	11:59	12:50	17:01	08:15		0.01		Thursday
30	48	7:00	12:09	12:50	16:31	08:50		0.37		Friday
31	49	7:47	12:02	14:39	16:31	06:07				Saturday
32	50	7:29	12:03	14:01	17:52	08:25		0.02		Sunday
33	51	6:56	12:19	14:39	17:07	07:51				Monday
34	52	7:29	12:03	14:01	17:52	08:25		0.02		Tuesday
35	53	6:56	12:19	14:39	17:07	07:51				Wednesday
36	54	7:55	11:59	12:50	17:01	08:15		0.01		Thursday
37	55	7:00	12:09	12:50	16:31	08:50		0.37		Friday
38	56	7:47	12:02	14:39	16:31	06:07				Saturday
39
40
41		Total OverTime (Hours)
42
43
44
26_XX 2020 To 26_XX 2020

Cell Formulas
Range	Formula
D4	D4	=TEXT(MIN($A$8:$A$37),"dd mmm.yyyy")&" to "&TEXT(MAX($A$8:$A$37),"dd mmm.yyyy")
A9:A38	A9	=IF(A8="","",IF(A8+1=EDATE($A$8,1),"",A8+1))
F8:F38	F8	=IF(A8="","",(C8-B8)+(E8-D8))
H8:H38	H8	=IF(A8="","",IF(OR(WEEKDAY(A8,16)=7,NOT(ISNA(MATCH(A8,$M$2:$M$99,0)))),F8,IF(F8>TIME(8,0,0),F8-TIME(8,0,0),"")))
J8:J38	J8	=TEXT(A8,"dddd")

 

[Toadstool]

My date format is mm/dd/yyyy but I think yours is dd/mm/yyyy.
Try typing 26/5/2020 into A8 and see what happens.

 

[abdelfattah]

what about col h where is the mistake

 

[Toadstool]

It is only a mistake because column A dates are not correct.
Type 26/5/2020 into A8 and see what happens.

 

[Dave Patton]

I quickly prepared an example based on Toadstool's post
- no merged cells
- dates dd-mmm-yy and formatted to day of week instead of separate column
- calculations based on decimal amounts not hours:minutes

T202006a.xlsm
	A	B	C	D	E	F	G	H
1								Time Card
2				Time Card
3
4		26-May-2020	to	25-Jun-2020
5
6		Morninig		After Noon
7		Log in	Log Out	Log in	Log Out	Hrs worked		OverTime Hours
8	Tue May-26-20	07:57	12:00	12:30	16:02	7.58
9	Wed May-27-20	07:50	11:59	12:38	16:08	7.65
10	Thu May-28-20	07:54	12:02	12:49	17:45	9.07		1.07
11	Fri May-29-20	10:56	11:56	12:51	15:44	3.88		3.88
12	Sat May-30-20	07:40	12:00	12:30	15:30	7.33
13	Sun May-31-20	08:00	12:00	12:30	16:06	7.60		7.60
14	Mon Jun-01-20	07:57	12:00	12:30	16:02	7.58
15	Tue Jun-02-20	07:50	11:59	12:38	16:08	7.65
16	Wed Jun-03-20	07:54	12:02	12:49	17:45	9.07		1.07
17	Thu Jun-04-20	07:53	11:54	12:52	18:32	9.68		1.68
18	Fri Jun-05-20	13:00	15:00			2.00		2.00
19	Sat Jun-06-20	07:55	12:01	12:32	18:37	10.18		2.18
20	Sun Jun-07-20	07:56	11:56	12:51	18:44	9.88		1.88
21	Mon Jun-08-20	08:02	11:58	12:50	18:04	9.17		1.17
22	Tue Jun-09-20	08:08	11:59	12:26	18:02	9.45		1.45
23	Wed Jun-10-20	07:00	12:09	12:50	16:31	8.83		0.83
24	Thu Jun-11-20	07:55	11:59	12:50	17:01	8.25		0.25
25	Fri Jun-12-20	07:00	12:09	12:50	16:31	8.83		8.83
26	Sat Jun-13-20	07:47	12:02	14:39	16:31	6.12
27	Sun Jun-14-20	07:29	12:03	14:01	17:52	8.42		0.42
28	Mon Jun-15-20	06:56	12:19	14:39	17:07	7.85
29	Tue Jun-16-20	07:55	11:59	12:50	17:01	8.25		0.25
30	Wed Jun-17-20	07:00	12:09	12:50	16:31	8.83		0.83
31	Thu Jun-18-20	07:47	12:02	14:39	16:31	6.12
32	Fri Jun-19-20	07:29	12:03	14:01	17:52	8.42		8.42
33	Sat Jun-20-20	06:56	12:19	14:39	17:07	7.85
34	Sun Jun-21-20	07:29	12:03	14:01	17:52	8.42		0.42
35	Mon Jun-22-20	06:56	12:19	14:39	17:07	7.85
36	Tue Jun-23-20	07:55	11:59	12:50	17:01	8.25		0.25
37	Wed Jun-24-20	07:00	12:09	12:50	16:31	8.83		0.83
38	Thu Jun-25-20	07:47	12:02	14:39	16:31	6.12
39
40
41		Totals			Hours	245.02	Overtime	45.32
42
7aa

Cell Formulas
Range	Formula
B4	B4	=MIN($A$8:$A$38)
D4	D4	=MAX($A$8:$A$38)
F8:F38	F8	=((C8-B8)+(E8-D8))*24
H8:H38	H8	=IF(OR(WEEKDAY(A8,16)=7,NOT(ISNA(MATCH(A8,$M$2:$M$99,0)))),F8,IF(F8>8,F8-8,""))
F41,H41	F41	=SUM(F8:F38)

 

[abdelfattah]

yes you right Toadstool thanks you're both