Code Required to calculate number of days worked, normal overtime and holiday overtime

[DocAElstein]

Hi Nelson,
So I assume you want to use the code that puts formulas in and adds a help column??? ( The second code , Sub IJAdjustKAddTotalAllWorksheet() )
( I see you have changed the row of the cells used to display the output to 34 )


So possibly we can use column L for the help column ?

_....................


In your after file, ( "File 2 After.xlsx" ) , you have given 26 Working days for Worksheet “121”:
Using Excel 2007 32 bit

Row\Col	B	C
34	TOTAL NO. OF DAYS ----->	26

Worksheet: 121


But this is your before, ( "File 1 Before.xlsx" ):
Using Excel 2007 32 bit

Row\Col	E	F		G
1	21.Dec.16	7:00	1	18:00
2	22.Dec.16	7:00	2	18:00
3	23.Dec.16	7:00	3	15:00
4	24.Dec.16	7:00	4	18:00
5	25.Dec.16	7:00	5	18:00
6	26.Dec.16	7:00	6	18:00
7	27.Dec.16	7:00	7	17:00
8	28.Dec.16	7:00	8	18:00
9	29.Dec.16
10	30.Dec.16
11	31.Dec.16
12	1.Jan.17	7:00	9	18:00
13	2.Jan.17	7:00	10	18:00
14	3.Jan.17	7:00	11	18:00
15	4.Jan.17	7:00	12	18:00
16	5.Jan.17	7:00	13	18:00
17	6.Jan.17
18	7.Jan.17	7:00	14	18:00
19	8.Jan.17	7:00	15	18:00
20	9.Jan.17
21	10.Jan.17	7:00	16	18:00
22	11.Jan.17	7:00	17	18:00
23	12.Jan.17	7:00	18	18:00
24	13.Jan.17	7:00	19	18:00
25	14.Jan.17	7:30	20	17:30
26	15.Jan.17	7:30	21	17:30
27	16.Jan.17	7:30	22	17:30
28	17.Jan.17	7:30	23	17:30
29	18.Jan.17	7:00	24	18:00
30	19.Jan.17	7:00	25	18:00
31	20.Jan.17
32

Worksheet: 121

Question _1)
???? Please explain why you give 26 ??? . I see 25 ????

_----____________________________________



Questions 2)

......(holiday is deducted if the person does not come the day before and after the holiday).....

I do not understand
_2a) Please give example to illustrate this

_ 2b) (i) What if the above situation arises, and the Holiday is the first or last day of the month ??? - Would you check the month before and after to see if a day was not worked???
_ 2b) (ii) You will need some external record set at the end of each month which is accessible in the next month to check each sheet for an absence on the last day of all Months.
_.....



You must make sure that your test data in Before and After illustrates all possible scenarios( and that it is correct ! )






Alan