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

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
985
Office Version
  1. 2010
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
ABCDEFGHIJ
1
2Time Card
3
426 XX.2020 To 25 XX.2020
5
6MorninigAfter Noon
7Log inLog OutLog inLog OutHrs workedOverTime HoursDay Name
8267:5712:0012:3016:02Tuesday
9277:5011:5912:3816:08Wednesday
10287:5412:0212:4917:45Thursday
112910:5611:5612:5115:44Friday
12307:4012:0012:3015:30Saturday
13318:0012:0012:3016:06Sunday
14
1517:5712:0012:3016:02Monday
1627:5011:5912:3816:08Tuesday
1737:5412:0212:4917:45Wednesday
1847:5311:5412:5218:32Thursday
19513:0015:00Friday
2067:5512:0112:3218:37Saturday
2177:5611:5612:5118:44Sunday
2288:0211:5812:5018:04Monday
2398:0811:5912:2618:02Tuesday
24107:0012:0912:5016:31Wednesday
25117:5511:5912:5017:01Thursday
26127:0012:0912:5016:31Friday
27137:4712:0214:3916:31Saturday
28147:2912:0314:0117:52Sunday
29156:5612:1914:3917:07Monday
30167:5511:5912:5017:01Tuesday
31177:0012:0912:5016:31Wednesday
32187:4712:0214:3916:31Thursday
33197:2912:0314:0117:52Friday
34206:5612:1914:3917:07Saturday
35217:2912:0314:0117:52Sunday
36226:5612:1914:3917:07Monday
37237:5511:5912:5017:01Tuesday
38247:0012:0912:5016:31Wednesday
39257:4712:0214:3916:31Thursday
40
41
42Total OverTime (Hours)
43
44
45
26_XX 2020 To 26_XX 2020
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
can you help me out with the Third Condition.. What do you mean..
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
985
Office Version
  1. 2010
what about you know about condition 3 to I explain for you differently way
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1HolidaysDate
2Time CardLabour Day01-May-20
3Eid al-Fitr24-May-20
426 May.2020 to 25 Jun.2020Eid al-Fitr25-May-20
5Test data31-May-20
6MorninigAfter NoonNational Day23-Jul-20
7Log inLog OutLog inLog OutHrs workedOverTime HoursDay Name
826-May7:5712:0012:3016:027:35 Tuesday
927-May7:5011:5912:3816:087:39 Wednesday
1028-May7:5412:0212:4917:459:041:04Thursday
1129-May10:5611:5612:5115:443:533:53Friday
1230-May7:4012:0012:3015:307:20 Saturday
1331-May8:0012:0012:3016:067:367:36Sunday
141-Jun7:5712:0012:3016:027:35 Monday
152-Jun7:5011:5912:3816:087:39 Tuesday
163-Jun7:5412:0212:4917:459:041:04Wednesday
174-Jun7:5311:5412:5218:329:411:41Thursday
185-Jun13:0015:002:002:00Friday
196-Jun7:5512:0112:3218:3710:112:11Saturday
207-Jun7:5611:5612:5118:449:531:53Sunday
218-Jun8:0211:5812:5018:049:101:10Monday
229-Jun8:0811:5912:2618:029:271:27Tuesday
2310-Jun7:0012:0912:5016:318:500:50Wednesday
2411-Jun7:5511:5912:5017:018:150:15Thursday
2512-Jun7:0012:0912:5016:318:508:50Friday
2613-Jun7:4712:0214:3916:316:07 Saturday
2714-Jun7:2912:0314:0117:528:250:25Sunday
2815-Jun6:5612:1914:3917:077:51 Monday
2916-Jun7:5511:5912:5017:018:150:15Tuesday
3017-Jun7:0012:0912:5016:318:500:50Wednesday
3118-Jun7:4712:0214:3916:316:07 Thursday
3219-Jun7:2912:0314:0117:528:258:25Friday
3320-Jun6:5612:1914:3917:077:51 Saturday
3421-Jun7:2912:0314:0117:528:250:25Sunday
3522-Jun6:5612:1914:3917:077:51 Monday
3623-Jun7:5511:5912:5017:018:150:15Tuesday
3724-Jun7:0012:0912:5016:318:500:50Wednesday
3825-Jun7:4712:0214:3916:316:07 Thursday
39
40
41TotalsHours245:01Overtime45:19
Sheet1
Cell Formulas
RangeFormula
B4B4=TEXT(MIN($A$8:$A$38),"dd mmm.yyyy")&" to "&TEXT(MAX($A$8:$A$38),"dd mmm.yyyy")
A9:A38A9=IF(A8="","",IF(A8+1=EDATE($A$8,1),"",A8+1))
F8:F38F8=IF(A8="","",(C8-B8)+(E8-D8))
H8:H38H8=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:J38J8=TEXT(A8,"dddd")
F41,H41F41=SUM(F8:F38)
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
985
Office Version
  1. 2010

ADVERTISEMENT

i need fix col a,h and d2 format of date not right as my picture
Work Time Sheet.xlsx
ABCDEFGHIJ
3
426 Jan.1900 to 24 Feb.1900
5
6MorninigAfter Noon
7Log inLog OutLog inLog OutHrs workedOverTime HoursDay Name
8267:5712:0012:3016:0207:35 Thursday
9277:5011:5912:3816:0807:390.32Friday
10287:5412:0212:4917:4509:040.04Saturday
112910:5611:5612:5115:4403:53 Sunday
12307:4012:0012:3015:3007:20 Monday
13318:0012:0012:3016:0607:36 Tuesday
14327:5712:0012:3016:0207:35 Wednesday
15337:5011:5912:3816:0807:39 Thursday
16347:5412:0212:4917:4509:040.38Friday
17357:5311:5412:5218:3209:410.07Saturday
183613:0015:0002:00 Sunday
19377:5512:0112:3218:3710:110.09Monday
20387:5611:5612:5118:4409:530.08Tuesday
21398:0211:5812:5018:0409:100.05Wednesday
22408:0811:5912:2618:0209:270.06Thursday
23417:0012:0912:5016:3108:500.37Friday
24427:5511:5912:5017:0108:150.01Saturday
25437:0012:0912:5016:3108:500.03Sunday
26447:4712:0214:3916:3106:07 Monday
27457:2912:0314:0117:5208:250.02Tuesday
28466:5612:1914:3917:0707:51 Wednesday
29477:5511:5912:5017:0108:150.01Thursday
30487:0012:0912:5016:3108:500.37Friday
31497:4712:0214:3916:3106:07 Saturday
32507:2912:0314:0117:5208:250.02Sunday
33516:5612:1914:3917:0707:51 Monday
34527:2912:0314:0117:5208:250.02Tuesday
35536:5612:1914:3917:0707:51 Wednesday
36547:5511:5912:5017:0108:150.01Thursday
37557:0012:0912:5016:3108:500.37Friday
38567:4712:0214:3916:3106:07 Saturday
39
40
41Total OverTime (Hours)
42
43
44
26_XX 2020 To 26_XX 2020
Cell Formulas
RangeFormula
D4D4=TEXT(MIN($A$8:$A$37),"dd mmm.yyyy")&" to "&TEXT(MAX($A$8:$A$37),"dd mmm.yyyy")
A9:A38A9=IF(A8="","",IF(A8+1=EDATE($A$8,1),"",A8+1))
F8:F38F8=IF(A8="","",(C8-B8)+(E8-D8))
H8:H38H8=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:J38J8=TEXT(A8,"dddd")
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
985
Office Version
  1. 2010

ADVERTISEMENT

what about col h where is the mistake
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. Windows
It is only a mistake because column A dates are not correct.
Type 26/5/2020 into A8 and see what happens.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDEFGH
1Time Card
2Time Card
3
426-May-2020to25-Jun-2020
5
6MorninigAfter Noon
7Log inLog OutLog inLog OutHrs workedOverTime Hours
8Tue May-26-2007:5712:0012:3016:027.58 
9Wed May-27-2007:5011:5912:3816:087.65 
10Thu May-28-2007:5412:0212:4917:459.071.07
11Fri May-29-2010:5611:5612:5115:443.883.88
12Sat May-30-2007:4012:0012:3015:307.33 
13Sun May-31-2008:0012:0012:3016:067.607.60
14Mon Jun-01-2007:5712:0012:3016:027.58 
15Tue Jun-02-2007:5011:5912:3816:087.65 
16Wed Jun-03-2007:5412:0212:4917:459.071.07
17Thu Jun-04-2007:5311:5412:5218:329.681.68
18Fri Jun-05-2013:0015:002.002.00
19Sat Jun-06-2007:5512:0112:3218:3710.182.18
20Sun Jun-07-2007:5611:5612:5118:449.881.88
21Mon Jun-08-2008:0211:5812:5018:049.171.17
22Tue Jun-09-2008:0811:5912:2618:029.451.45
23Wed Jun-10-2007:0012:0912:5016:318.830.83
24Thu Jun-11-2007:5511:5912:5017:018.250.25
25Fri Jun-12-2007:0012:0912:5016:318.838.83
26Sat Jun-13-2007:4712:0214:3916:316.12 
27Sun Jun-14-2007:2912:0314:0117:528.420.42
28Mon Jun-15-2006:5612:1914:3917:077.85 
29Tue Jun-16-2007:5511:5912:5017:018.250.25
30Wed Jun-17-2007:0012:0912:5016:318.830.83
31Thu Jun-18-2007:4712:0214:3916:316.12 
32Fri Jun-19-2007:2912:0314:0117:528.428.42
33Sat Jun-20-2006:5612:1914:3917:077.85 
34Sun Jun-21-2007:2912:0314:0117:528.420.42
35Mon Jun-22-2006:5612:1914:3917:077.85 
36Tue Jun-23-2007:5511:5912:5017:018.250.25
37Wed Jun-24-2007:0012:0912:5016:318.830.83
38Thu Jun-25-2007:4712:0214:3916:316.12 
39
40
41TotalsHours245.02Overtime45.32
42
7aa
Cell Formulas
RangeFormula
B4B4=MIN($A$8:$A$38)
D4D4=MAX($A$8:$A$38)
F8:F38F8=((C8-B8)+(E8-D8))*24
H8:H38H8=IF(OR(WEEKDAY(A8,16)=7,NOT(ISNA(MATCH(A8,$M$2:$M$99,0)))),F8,IF(F8>8,F8-8,""))
F41,H41F41=SUM(F8:F38)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top