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

**nelsondavid** · 02-02-2017, 07:41 PM

Dear Alan,

As requested two files attached .

Thanks,

Nelson

**DocAElstein** · 02-03-2017, 05:01 AM

Hi Nelson,

Originally Posted by nelsondavid

..As requested two files attached .....

OK , I think I understand.
I will post you a solution later

**DocAElstein** · 02-03-2017, 07:18 PM

Hi Nelson,

I hope I have the correct Files and understand the Logic: I see with the last Files you sent that:
Two things are happening,
___ Totals are made and pasted out
And
___ Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are to be changed

So I this is what I understand is the requirement and have done:

Rem 1) The code is set to work on the Active Workbook. That is the one you “looking at”. ( Any Worksheet can be selected )

Rem 2) Total No, of days is simply taken as a constant of 30. (Const TDays As Long = 30)

'3b)
_ Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations

_ Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ??
_ Overtime ( Column J ) is needed as it will be changed and then used in calculations.

'3d) We need to check for a Holy?? Holiday?? Friday??
___If so Then
____ Total Hrs ( Column H ) are added to Overtime Hrs ( Column J ) according to the following rules:
____ (i) If Total Hrs are less than or equal to 9 , Then all Total Hrs are Given To to Overtime Hrs
____ (ii) Else If Total Hrs are greater than 9 , Then ( Total Hrs – 1 ) are Given To to Overtime Hrs

____ (iii) Normal Hrs ( Column I ) are then Emptyed.

_3e)
_ As in all my codes, Normal Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are Normal hours are in column I.
( The total number will be entered into cell G34 )

_ As in all my codes, Holiday Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are no Normal hours are in column I.
( The total number will entered into cell J34 )

_3f)
The total number Normal Overtime entered into cell G34
The total number Holiday Overtime entered into cell G34
The value of 30 for Total days is simply added to cell C34

_ 3g Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed
_...

_._______________________________

'_- ExPlanations are given in code ' Comments, ( especially for snb ; ) )

Code is here: ( Post #19 )
http://www.excelfox.com/forum/showth...0066#post10066
http://www.excelfox.com/forum/showth...age2#post10066

Typical Results for one Worksheet are given in next 2 Posts

Alan

**DocAElstein** · 02-03-2017, 07:20 PM

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
32
33
34		TOTAL NO. OF DAYS ----->				Normal Overtime ----->			Holiday Overtime ----->

Worksheet: 121

**DocAElstein** · 02-03-2017, 07:21 PM

Typical After

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		8: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		10: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		10: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
32
33
34		TOTAL NO. OF DAYS ----->	30			Normal Overtime ----->	47		Holiday Overtime ----->	28