Found Dates between Gaps

[vishal0589]

Hi Friends,
I am working with a attendance register where I have to check gap between two subsequent Weekly Offs (W/O) with following conditions.
-> There must be 4 Present (P) between two Weekly Offs (W/O).
-> If Minimum 4 present (P) is not available between two Weekly Offs (W/O), dates of W/O will be resulted in columns

for example.

S. No.

Emp. No.

1

2

3

4

5

6

7

8

9

10

11

12

13

Output

1

143

P

P

P

P

W/O

P

P

W/O

P

P

P

P

W/O

5

8

2

499

P

W/O

P

P

P

P

W/O

P

P

P

EL

EL

W/O

7

13

3

501

P

P

P

P

CL

P

P

P

P

W/O

P

P

P

4

505

P

W/O

P

P

P

P

W/O

CL

P

P

P

W/O

P

7

12

5

506

P

P

CL

P

P

W/O

P

EL

P

P

W/O

P

P

6

11

6

509

P

W/O

P

P

P

P

W/O

P

P

P

P

CL

P

7

758

P

W/O

P

CL

CL

P

W/O

P

P

CL

P

W/O

P

2

7

12

<colgroup><col span="2"><col><col span="17"></colgroup><tbody>
</tbody>

<colgroup><col span="2"><col><col><col span="18"></colgroup><tbody>
</tbody>

Please help me out.

Thanks in Advance.

CMA Vishal Srivastava

 

[vishal0589]

Hi Bob,
Waiting for your response.

 

[oldbrewer]

b ecause I am not clear what is wanted - surely "holiday" is taken when the employee wants it - not when you say......

 

[vishal0589]

Hi,
What I want to assign W/O to employees for regulatory purpose and attendance is also for imaginary figures.

As stated in previous example, Suppose we have 30 days attendance cycle and we have assigned Presents (P), EL, CL, H ,etc to every employee on random basis.

But as far as W/O is concerned we want to assign W/O only when Employee have completed at least 4 P (Presents).

You can override existing values with W/O.

 

[oldbrewer]

at least 4 is 4 or 5 or 6 or 7 or 8..........

so what is the rule for assigning W/O

we could have a column on the right of the data saying how many W/O's an employee has POTENTIALLY

eg he could earn 3 because he has 14 p's yet has not yet had a W/O

 

[vishal0589]

1) There must be minimum 4 Presents (P) between two Weekly Offs (W/O)

2) 3-4 Weekly Offs (W/O) could be assigned in a 30 days period.

e.g:
We have assigned following attendance:

[TABLE="width: 1989"]
<colgroup><col><col><col span="29"></colgroup><tbody>[TR]
[TD]Emp. No.[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]506[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]509[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]758[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

Now the result what I want to assign W/O like as below:

[TABLE="width: 1989"]
<colgroup><col><col><col span="29"></colgroup><tbody>[TR]
[TD]Emp. No.[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]506[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]509[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]758[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]CL[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]EL[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]W/O[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

Provided W/O replaces Presents (P) only.


Hope you understand.

 

[oldbrewer]

how can you have 4 weekly offs in 30 days?

 

[vishal0589]

It is a regulatory requirement. In 30 days period 3-4 Weekly off (W/O) should be given to each employee.

Weekly offs (W/O) could be from any day in 30 days period

 

[oldbrewer]

S. No.

Emp. No.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

W/O total

<3

>4

1

143

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

P

P

P

P

3

2

499

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

P

P

P

3

3

501

P

P

P

P

CL

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

P

P

3

4

505

P

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

P

3

5

506

P

P

CL

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

3

6

509

P

P

P

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

3

7

758

P

P

P

CL

CL

P

P

P

P

P

W/O

P

P

P

P

P

P

W/O

P

P

P

P

P

P

P

P

P

P

P

P

2

YES

20

ERROR

each person must have 3 or 4 W/O's

7 persons

=21 to 28 W/O's

THIS RANDOM PATTERN OF ASSIGNMENT

SHOWS Emp 758 needs one more w/o

then compliance is achieved

the last 2 columns show if number of W/O

is outside requirements

the total 20 shows that at least one more W/O is due

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[vishal0589]

That is fine Bob,

But we have to assign 3-4 weekly offs to each employee and 100% compliance is must

 

[oldbrewer]

I was putting forward an idea - a pattern whose shape is dependent on the number of employees - blocks of 5 employees come to mind - and more about 3 or 4 - why cannot it be 3 one month and 4 the next month - with excel you have to define rules....