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

[nelsondavid]

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]

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

[nelsondavid]

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

[DocAElstein]

Hi Nelson
_1)

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

This makes no sense ??? I have no idea what you are saying ???


_2)
I think you have wrong data again ! ! !

Your after “File 4 After.xlsx”

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

Worksheet: 121

Your Before File “File 3 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
31	20.Jan.17

Worksheet: 121


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


_2)
The test data that you have chosen does not test this scenario “...holiday is deducted if the person does not come the day before and after the holiday...”

Possibly you mean something like this ???

				29.Dec.16						ABSENT
				30.Dec.16	7:00	18:00	11:00	9:00	2:00
				31.Dec.16						ABSENT

Worksheet: 121
????

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

Try again !!!

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

[nelsondavid]

Dear Alan,

Files attached again and also in column K absent days should be marked as ABSENT

Rest all seems to be fine

Thanks,

Nelson

[DocAElstein]

Hi Nelson

Files attached again and also in column K absent days should be marked as ABSENT..

This is a new requirement ?

But I understand and can do it

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

_1)

....Rest all seems to be fine...

No rest is still NOT fine

I count 24 days in Worksheet "121" ( "File 3 Before.xlsx" = 24 days worked)

You give 25 ????

Please explain why you give 25 ????



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

_2)
The test data that you have chosen does not test this scenario “...holiday is deducted if the person does not come the day before and after the holiday...”


Let me try to explain to you what you possibly want ( = ..holiday is deducted if the person does not come the day before and after the holiday... )

If Before =

Row\Col	E	F	G	H	I	J
9	29.Dec.16
10	30.Dec.16	7:00	18:00	11:00	9:00	2:00
11	31.Dec.16

Worksheet: 121Before

Then After =

Row\Col	E	F	G	H	I	J
9	29.Dec.16
10	30.Dec.16	7:00	18:00	11:00	9:00	2:00
11	31.Dec.16

Worksheet: 121After

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

Else IF Before =

Row\Col	E	F	G	H	I	J
9	29.Dec.16	7:00	18:00	11:00	9:00	2:00
10	30.Dec.16	7:00	18:00	11:00	9:00	2:00
11	31.Dec.16

Worksheet: 121Before

Then After =

Row\Col	E	F	G	H	I	J
9	29.Dec.16	7:00	18:00	11:00	9:00	2:00
10	30.Dec.16	7:00	18:00	11:00		10:00
11	31.Dec.16

Worksheet: 121After

Is this correct?





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

Try again !!!

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

Alan

P.s. I have had a code for you finished now for many days: But I cannot test it until you give me correct info and data !!!!!!
I am very confused, why you keep giving me wrong and inappropriate data ????


P.P.s. You are using the second code. It uses the formulas suggested by Admin. It need a help column which the code puts in. It did put those in column K. But you want that for the ABSENT indication. So I suggested that could be in column L. I had no response and I do not know why am writing this as I expect you have no idea what I am saying. Never mind. I expect we will get there in the end.

[nelsondavid]

Dear Alan,

TOTAL NO. OF DAYS ----->25 ,i.e, Absent 4 days plus one holiday (5 days) - Absent days are 29,31,9,19 and 30 (the employee did not come on 29 and 31 so 30 holiday marked as absent).......

Rest explained in attached file

Thanks,

Nelson