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

 

[oldbrewer]

employee 143 has week 5 off, if he had only worked one week and asked for another week off this would have been refused presumably, so you just want week numbers where employee took the week off ?

 

[vishal0589]

This is what we are looking for. We have to check whether employee have completed atleast 4 Present (P) between two Weekly OFFs.

If 4 P not available then Weekly off dates should be resulted.

 

[oldbrewer]

S. No.

Emp. No.

W1

W2

W3

W4

W5

W6

W7

W8

W9

W10

W11

W12

W13

Output

13

WEEK PERIOD

1

143

P

P

P

P

W/O

P

P

W/O

P

P

P

P

W/O

W5

W8

W13

2

499

P

W/O

P

P

P

P

W/O

P

P

P

EL

EL

W/O

W2

W7

W13

3

501

P

P

P

P

CL

P

P

P

P

W/O

P

P

P

W10

4

505

P

W/O

P

P

P

P

W/O

CL

P

P

P

W/O

P

W2

W7

W12

5

506

P

P

CL

P

P

W/O

P

EL

P

P

W/O

P

P

W6

W11

6

509

P

W/O

P

P

P

P

W/O

P

P

P

P

CL

P

W2

W7

7

758

P

W/O

P

CL

CL

P

W/O

P

P

CL

P

W/O

P

W2

W7

W12

this macro runs through the data and produces the output table

Dim aa(25)

For k = 2 To 8

For j = 3 To 3 + Cells(1, 21) - 1

If Cells(k, j) = "W/O" Then Sum = Sum + 1: aa(Sum) = Cells(1, j)

Next j

If Sum = 0 Then GoTo 50

For b = 17 To 17 + Sum

tot = tot + 1

Cells(k, b) = aa(tot)

Next b

For c = 1 To 25

aa(c) = ""

Next c

tot = 0

Sum = 0

50 Next k

End Sub

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

 

[vishal0589]

Hi,
I think there is problem in implementing your macro. As I am not able to use it. If any thing i m missing pls expain..

 

[oldbrewer]

i wrote it and tested it - it produces the output shown in my post - if you PM me with your Email I am happy to send the spreadsheet across. This action does not breach forum guidelines as a solution is already posted.

 

[vishal0589]

Also it seems there is an error in your solution.

I just want to know those Weekno where 4 Presents where not available between two subsequent Weekly offs (W/O).

Your macro gives all Weeknos.

 

[oldbrewer]

I said PM me - now the world knowds your Email !!!!!

 

[vishal0589]

No Reply Yet!

 

[oldbrewer]

I have sent the email and it did not bounce..........

but it is true I misread the question - you want to know if there are 3 or less P's between a pair of W/O 's