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-05-2017, 07:40 PM

Output values and formulas for output range for Worksheet "121" after running second code
Sub IJAdjustKAddTotalAllWorksheet()
( Second code is here: http://www.excelfox.com/forum/showth...0072#post10072

Output values as seen in the spreadsheet:Using Excel 2007 32 bit

_Row\^Col	B	C	F	G	I	J
33
34	TOTAL NO. OF DAYS ----->	30	Normal Overtime ----->		Holiday Overtime ----->
35				47		28
36
37	For all Month no. of days we take as 30 only

Worksheet: 121

Formula string values ( as seen in the Formula Bar ) :Using Excel 2007 32 bit

_Row\^Col	B	C	F	G	I	J
33
34	TOTAL NO. OF DAYS ----->	30	Normal Overtime ----->		Holiday Overtime ----->
35				=SUMIF(K1:K31,"N",J1:J31)*24		=SUMIF(K1:K31,"H",J1:J31)*24
36
37	For all Month no. of days we take as 30 only

Worksheet: 121

There you go

. lots for you to try

before you tell us you wanted something different !

वहाँ आप का प्रयास करने के लिए बहुत सारे हैं।

संभवत: आप कुछ अलग चाहते हो सकता है

Alan

**nelsondavid** · 02-06-2017, 07:12 AM

Dear Alan,

The code seems to be working fine

..i need normal OT and Holiday OT to appear exactly in cell G34 and J34
Can i use the same code for every month and if yes what changes i need to do from my end
I have around 800 sheets ...will the code work on all sheets in one go

Thanks,

Nelson

**DocAElstein** · 02-06-2017, 03:40 PM

Hi Nelson,

Originally Posted by nelsondavid

..i need normal OT and Holiday OT to appear exactly in cell G34 and J34 ...

I think you can see what part of the code determines where the output goes

Code:

'3f) Paste out final Totals and days to current Worksheet
     Let wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a day
     Let wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a day
     Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal   Hrs  ( Column I ) and Overtime Hrs ( Column J ) are  changed

Code:

'3f) Paste out final Totals formulas and days to current Worksheet
     Let wsStear.Range("G35").Value = "=SUMIF(K1:K" & lr & ",""N"",J1:J" & lr & ")*24"
     Let wsStear.Range("J35").Value = "=SUMIF(K1:K" & lr & ",""H"",J1:J" & lr & ")*24"
     Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal   Hrs  ( Column I ) and Overtime Hrs ( Column J ) are  changed ' And can paste out help column if you like

I am sure you can understand how to modify to change which cells are used for where the total appear
_............................................

Originally Posted by nelsondavid

.... Can i use the same code for every month ...

Yes..

Originally Posted by nelsondavid

. if yes what changes i need to do from my end..

..The code is not in any way dependant on any month information......There is no reason that I can see to need to change anything..

Originally Posted by nelsondavid

....what changes i need to do from my end..

You should not need to change your end, leave it alone, at least as far as any effect on the Months are concerned
_................................................. .......

Originally Posted by nelsondavid

...I have around 800 sheets ...will the code work on all sheets in one go...

The code is designed to work on multiple worksheets.
Obviously I only tested it with two.
If I were you, I would test it first with a larger amount, 10, 30, 60, .100 .......etc.. then finally 800 etc.

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

In my opinion, it is very important that you try to understand the code...

Alan.

P.s.

Originally Posted by nelsondavid

..The code seems to be working fine..

We have given you 2 codes
From Alan
http://www.excelfox.com/forum/showth...age2#post10066

From Alan and Admin
http://www.excelfox.com/forum/showth...age2#post10072

**nelsondavid** · 02-06-2017, 09:55 PM

Dear Nelson,

The code working fine , but have two problems 1)It calculates the number of days worked as 30 even if the person absent for one or two days

2)I use column K for remarks which you are currently using to identify the days

Sample attached

Thanks,

Nelson

**DocAElstein** · 02-06-2017, 11:01 PM

Originally Posted by nelsondavid

The code working fine , but have two problems 1)It calculates the number of days worked as 30 even if the person absent for one or two days...

Hi Nelson
I have 5 questions ( _1a) 1b) 2a) 2b) 3) ) for you:
_1) you told me that days are always 30 ??
_1a) How do you now want Days calculated? ?????

( This is a new requirement I expect? Your original Before had no entries in column
_1b) Is the requirement for taking into account absence new? ( or did you forget it originally) ?? )

_2a) Which code are you using ?????
( You have 2

Originally Posted by DocAElstein

.......
P.s.
We have given you 2 codes
From Alan
Post #19 here: http://www.excelfox.com/forum/showth...age2#post10066

From Alan and Admin
Post #20 here: http://www.excelfox.com/forum/showth...age2#post10072

Which are you using? Which do you prefer?? ????

_2b) Which do you prefer?? ????

-3) Are you sure that you do not require a header row ???
Do you require a header row as in your first post here: File Sample2.xlxs http://www.excelfox.com/forum/showth...0058#post10058

Header.jpg http://imgur.com/1Dvfv6N
Header.JPG

Alan

**nelsondavid** · 02-07-2017, 07:56 AM

Dear Alan,

Yes i actually forget to tell you about the absent days.What i mean "Every month we consider of 30 days , if the month is of 30 or 31 or like 28 days Feb all these months we take it as 30 days only.
Total no. of days is the no. of days the person worked plus the holidays(holiday is deducted if the person does not come the day before and after the holiday)

Header row not required

Have attached sample files and the code am using now

thanks,

Nelson

**DocAElstein** · 02-07-2017, 04:26 PM

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)

Originally Posted by nelsondavid

......(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

**nelsondavid** · 02-07-2017, 10:03 PM

Dear Alan,

25 days ABSENT ,i.e, Absent 4 days plus one holiday (4 days) -29,31,9,19 and 30 (the employee did not come on 29 and 31 so 30 holiday marked as absent)

If Question 2 scenario arises holiday is considered present

Sample attached

Thanks,

Nelson