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

**DocAElstein** · 02-05-2017, 07:27 PM

BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
(as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068 )

Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G	H	I	J
1	SOVEL	JOY	121	TEAM LEADER	21.Dec.16	7:00	18:00	11:00	9:00	2:00
2	SOVEL	JOY	121	TEAM LEADER	22.Dec.16	7:00	18:00	11:00	9:00	2:00
3	SOVEL	JOY	121	TEAM LEADER	23.Dec.16	7:00	15:00	8:00	9:00	0:00
4	SOVEL	JOY	121	TEAM LEADER	24.Dec.16	7:00	18:00	11:00	9:00	2:00
5	SOVEL	JOY	121	TEAM LEADER	25.Dec.16	7:00	18:00	11:00	9:00	2:00
6	SOVEL	JOY	121	TEAM LEADER	26.Dec.16	7:00	18:00	11:00	9:00	2:00
7	SOVEL	JOY	121	TEAM LEADER	27.Dec.16	7:00	17:00	10:00	9:00	1:00
8	SOVEL	JOY	121	TEAM LEADER	28.Dec.16	7:00	18:00	11:00	9:00	2:00
9	SOVEL	JOY	121	TEAM LEADER	29.Dec.16	7:00	18:00	11:00	9:00	2:00
10	SOVEL	JOY	121	TEAM LEADER	30.Dec.16	7:00	18:00	11:00	9:00	2:00
11	SOVEL	JOY	121	TEAM LEADER	31.Dec.16	7:00	18:00	11:00	9:00	2:00
12	SOVEL	JOY	121	TEAM LEADER	1.Jan.17	7:00	18:00	11:00	9:00	2:00
13	SOVEL	JOY	121	TEAM LEADER	2.Jan.17	7:00	18:00	11:00	9:00	2:00
14	SOVEL	JOY	121	TEAM LEADER	3.Jan.17	7:00	18:00	11:00	9:00	2:00
15	SOVEL	JOY	121	TEAM LEADER	4.Jan.17	7:00	18:00	11:00	9:00	2:00
16	SOVEL	JOY	121	TEAM LEADER	5.Jan.17	7:00	18:00	11:00	9:00	2:00
17	SOVEL	JOY	121	TEAM LEADER	6.Jan.17
18	SOVEL	JOY	121	TEAM LEADER	7.Jan.17	7:00	18:00	11:00	9:00	2:00
19	SOVEL	JOY	121	TEAM LEADER	8.Jan.17	7:00	18:00	11:00	9:00	2:00
20	SOVEL	JOY	121	TEAM LEADER	9.Jan.17	7:00	18:00	11:00	9:00	2:00
21	SOVEL	JOY	121	TEAM LEADER	10.Jan.17	7:00	18:00	11:00	9:00	2:00
22	SOVEL	JOY	121	TEAM LEADER	11.Jan.17	7:00	18:00	11:00	9:00	2:00
23	SOVEL	JOY	121	TEAM LEADER	12.Jan.17	7:00	18:00	11:00	9:00	2:00
24	SOVEL	JOY	121	TEAM LEADER	13.Jan.17	7:00	18:00	11:00	9:00	2:00
25	SOVEL	JOY	121	TEAM LEADER	14.Jan.17	7:30	17:30	10:00	9:00	1:00
26	SOVEL	JOY	121	TEAM LEADER	15.Jan.17	7:30	17:30	10:00	9:00	1:00
27	SOVEL	JOY	121	TEAM LEADER	16.Jan.17	7:30	17:30	10:00	9:00	1:00
28	SOVEL	JOY	121	TEAM LEADER	17.Jan.17	7:30	17:30	10:00	9:00	1:00
29	SOVEL	JOY	121	TEAM LEADER	18.Jan.17	7:00	18:00	11:00	9:00	2:00
30	SOVEL	JOY	121	TEAM LEADER	19.Jan.17	7:00	18:00	11:00	9:00	2:00
31	SOVEL	JOY	121	TEAM LEADER	20.Jan.17

Worksheet: 121

**DocAElstein** · 02-05-2017, 07:30 PM

Output data range ( AFTER ) for worksheet “121” after running either of the codes
Sub IJAdjustTotalAllWorksheet()
Or
Sub IJAdjustKAddTotalAllWorksheet()

Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	SOVEL	JOY	121	TEAM LEADER	21.Dec.16	7:00	18:00	11:00	9:00	2:00	N
2	SOVEL	JOY	121	TEAM LEADER	22.Dec.16	7:00	18:00	11:00	9:00	2:00	N
3	SOVEL	JOY	121	TEAM LEADER	23.Dec.16	7:00	15:00	8:00		8:00	H
4	SOVEL	JOY	121	TEAM LEADER	24.Dec.16	7:00	18:00	11:00	9:00	2:00	N
5	SOVEL	JOY	121	TEAM LEADER	25.Dec.16	7:00	18:00	11:00	9:00	2:00	N
6	SOVEL	JOY	121	TEAM LEADER	26.Dec.16	7:00	18:00	11:00	9:00	2:00	N
7	SOVEL	JOY	121	TEAM LEADER	27.Dec.16	7:00	17:00	10:00	9:00	1:00	N
8	SOVEL	JOY	121	TEAM LEADER	28.Dec.16	7:00	18:00	11:00	9:00	2:00	N
9	SOVEL	JOY	121	TEAM LEADER	29.Dec.16	7:00	18:00	11:00	9:00	2:00	N
10	SOVEL	JOY	121	TEAM LEADER	30.Dec.16	7:00	18:00	11:00		10:00	H
11	SOVEL	JOY	121	TEAM LEADER	31.Dec.16	7:00	18:00	11:00	9:00	2:00	N
12	SOVEL	JOY	121	TEAM LEADER	1.Jan.17	7:00	18:00	11:00	9:00	2:00	N
13	SOVEL	JOY	121	TEAM LEADER	2.Jan.17	7:00	18:00	11:00	9:00	2:00	N
14	SOVEL	JOY	121	TEAM LEADER	3.Jan.17	7:00	18:00	11:00	9:00	2:00	N
15	SOVEL	JOY	121	TEAM LEADER	4.Jan.17	7:00	18:00	11:00	9:00	2:00	N
16	SOVEL	JOY	121	TEAM LEADER	5.Jan.17	7:00	18:00	11:00	9:00	2:00	N
17	SOVEL	JOY	121	TEAM LEADER	6.Jan.17						H
18	SOVEL	JOY	121	TEAM LEADER	7.Jan.17	7:00	18:00	11:00	9:00	2:00	N
19	SOVEL	JOY	121	TEAM LEADER	8.Jan.17	7:00	18:00	11:00	9:00	2:00	N
20	SOVEL	JOY	121	TEAM LEADER	9.Jan.17	7:00	18:00	11:00	9:00	2:00	N
21	SOVEL	JOY	121	TEAM LEADER	10.Jan.17	7:00	18:00	11:00	9:00	2:00	N
22	SOVEL	JOY	121	TEAM LEADER	11.Jan.17	7:00	18:00	11:00	9:00	2:00	N
23	SOVEL	JOY	121	TEAM LEADER	12.Jan.17	7:00	18:00	11:00	9:00	2:00	N
24	SOVEL	JOY	121	TEAM LEADER	13.Jan.17	7:00	18:00	11:00		10:00	H
25	SOVEL	JOY	121	TEAM LEADER	14.Jan.17	7:30	17:30	10:00	9:00	1:00	N
26	SOVEL	JOY	121	TEAM LEADER	15.Jan.17	7:30	17:30	10:00	9:00	1:00	N
27	SOVEL	JOY	121	TEAM LEADER	16.Jan.17	7:30	17:30	10:00	9:00	1:00	N
28	SOVEL	JOY	121	TEAM LEADER	17.Jan.17	7:30	17:30	10:00	9:00	1:00	N
29	SOVEL	JOY	121	TEAM LEADER	18.Jan.17	7:00	18:00	11:00	9:00	2:00	N
30	SOVEL	JOY	121	TEAM LEADER	19.Jan.17	7:00	18:00	11:00	9:00	2:00	N
31	SOVEL	JOY	121	TEAM LEADER	20.Jan.17						H

Worksheet: 121

**DocAElstein** · 02-08-2017, 11:33 PM

These are the some of the criteria my next code will work on: ( These are those criteria which were unclear until now ! )

Holidays are shown in yellow background color

Normal working days have no background color

The data rows to be considered are those filled with dates in column E

“TOTAL NO. OF DAYS” ( to be placed by the code in cell C34 )
For all Months, the “TOTAL NO. OF DAYS” ( to be placed in cell C34 ) is not necessarily the number of days worked.
The formula for calculating this is:
_Assuming the employee is not Absent for any day, then the “TOTAL NO. OF DAYS” is always taken as 30
_ If the employee has one or more normal days of absence, ( normal days with no total working hours ), then the formula for calculating “TOTAL NO. OF DAYS” is as follows:
TOTAL NO. OF DAYS = 30 – ( Count of “ABSENT” )

“ABSENT” ( to be written in some rows in Column K by the program )
Count of “ABSENT” is the number of occurrences of ABSENT in column K in the final ( After ) Worksheet “ABSENT” is to be written in some rows of column K by the code under certain criteria.

( “ABSENT” is not necessarily the normal working days in which an employee is absent and / or has no total working hours. )

“ABSENT” is to be written in column K by the code under the following criteria:
_ For the rows of all normal days when the employee has no working hours, ( days when the employee is absent ), “ABSENT” is to be written in column K.
_ In addition , should it occur that an employee is absent for both the days before and after a holiday, then for the ( Holiday ) row in between those two days, “ABSENT” is to be written in column K.
( No consideration of this ““ABSENT” criteria thereof” is made for the case of a Holiday at the first or last “Entries” )

( The number of “Entries” is taken as the filled dates in column E )

Column L ( help column )
Nelson has chosen the second code. It puts formulas in cells C34, G34, and J34.
This requires “H” or “N” to indicate Holiday or Normal working day. This will be written by the code in column L

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

Nelson:
Please examine the above criteria , and tell me if this is correct ?
Alan

**DocAElstein** · 02-09-2017, 05:43 PM

Hi Nelson.

Here is the next code version:
Here: ( Post 27 )
http://www.excelfox.com/forum/showth...0101#post10101

and here: ( Post 28 )
http://www.excelfox.com/forum/showth...0102#post10102

It is all one code ( I had to split it due to post character size restrictions )
You need to copy the second ´part directly under the first part in the same code module

_.........

The code takes your Before like these:
http://www.excelfox.com/forum/showth...0096#post10096
http://www.excelfox.com/forum/showth...0097#post10097

After running_..
Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()
_.. you should get like:
http://www.excelfox.com/forum/showth...0098#post10098
http://www.excelfox.com/forum/showth...0099#post10099
http://www.excelfox.com/forum/showth...0100#post10100

_.....

Here is just a small sample of what the code does:
Before:
Using Excel 2007 32 bit

28.Dec.16	7:00	18:00	11:00	9:00	2:00
29.Dec.16
30.Dec.16
31.Dec.16
1.Jan.17	7:00	18:00	11:00	9:00	2:00

12.Jan.17	7:00	18:00	11:00	9:00	2:00
13.Jan.17	7:00	18:00	11:00	9:00	2:00
14.Jan.17	7:30	17:30	10:00	9:00	1:00

Worksheet: Post22Before121

After:

28.Dec.16	7:00	18:00	11:00	9:00	2:00		N
29.Dec.16						ABSENT	N
30.Dec.16						ABSENT
31.Dec.16						ABSENT	N
1.Jan.17	7:00	18:00	11:00	9:00	2:00		N

9.Jan.17						ABSENT	N
10.Jan.17	7:00	18:00	11:00	9:00	2:00		N
11.Jan.17	7:00	18:00	11:00	9:00	2:00		N
12.Jan.17	7:00	18:00	11:00	9:00	2:00		N
13.Jan.17	7:00	18:00	11:00		10:00		H
14.Jan.17	7:30	17:30	10:00	9:00	1:00		N

_Row\^Col	B	C	D	E	F	G	H	I	J
34	TOTAL NO. OF DAYS ----->	25			Normal Overtime ----->	39		Holiday Overtime ----->	18

_Row\^Col	B	C	D	E	F	G	H	I	J
34	TOTAL NO. OF DAYS ----->	=30-COUNTIF(K1:K31,"ABSENT")			Normal Overtime ----->	*=SUMIF(L1:L31,"N",J1:J31)24**		Holiday Overtime ----->	*=SUMIF(L1:L31,"H",J1:J31)24**

Alan

**nelsondavid** · 02-09-2017, 08:09 PM

Dear Alan

,

Thanks a Lot , it seems to be working fine..if find any problem will get in touch with you

Thanks and appreciate your patience to solve the problem

Nelson