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

**DocAElstein** · 02-08-2017, 03:20 AM

Hi Nelson
_1)

Originally Posted by nelsondavid

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

Originally Posted by DocAElstein

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

**nelsondavid** · 02-08-2017, 05:34 AM

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** · 02-08-2017, 04:31 PM

Hi Nelson

Originally Posted by nelsondavid

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)

Originally Posted by nelsondavid

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

Originally Posted by DocAElstein

.....
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** · 02-08-2017, 09:41 PM

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** · 02-08-2017, 10:25 PM

Hi Nelson,

Originally Posted by nelsondavid

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

Code:

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

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

Originally Posted by nelsondavid

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

**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