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

[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

[DocAElstein]

Hi Nelson,

...(the employee did not come on 29 and 31 so 30 holiday marked as absent)......

This I now understand ( It was impossible to know this as the code was not putting ABSENT in. I can do this and understand now

Using Excel 2007 32 bit
BEFORE







29.Dec.16







30.Dec.16







31.Dec.16































AFTER







29.Dec.16





ABSENT

30.Dec.16





ABSENT

31.Dec.16





ABSENT


Worksheet: Sheet1

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

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

OK This makes sense now
( You forgot to put ABSENT in 30. December , ( "File 4 After.xlsx" )
This was your After ( "File 4 After.xlsx" )

29.Dec.16						ABSENT
30.Dec.16
31.Dec.16						ABSENT

Worksheet: 121

It should have been this !!!

29.Dec.16						ABSENT
30.Dec.16						ABSENT
31.Dec.16						ABSENT

Worksheet: 121
_............................

I think I understand now
I will have time to look again at this tomorrow



Alan