If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
Also, to ensure that your UserName is registered, please ensure that you have confirmed your e-mail address by clicking on the link that will be sent to your address. Users who do not confirm their e-mail addresses will be able to browse the forum, but not able to download any content

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

I think we may almost know what is to be done !

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

ExcelFox is Not Associated With Microsoft®. ExcelFox Retains the Rights to ALL Posts and Threads. Fluent is a trademark of Microsoft Corporation and the Fluent user interface is licensed from Microsoft Corporation.