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]

Yes your Idea is good but the matter of fact is that attendance would not require YTD analysis. So month to month variations in Weekly Off is not a problem. We may give 3 W/O in one month and 3 or 4 in another one.

Right now, I need to randomize the monthly attendance register. Need a macro to deal with.

 

[oldbrewer]

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	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60
		X									X									X									X						X										X										X
			X									X									X									X						X										X										X
				X									X									X									X						X										X										X
					X									X									X									X						X										X										X
						X									X									X									X						X										X										X
		X									X									X									X						X										X										X
			X									X									X									X						X										X										X
				X									X									X									X						X										X										X
					X									X									X									X						X										X										X
						X									X									X									X						X										X										X
			THIS PATTERN FOR BLOCKS OF 5 EMPLOYEES ENSURES 7 W/O's IN A PERIOD OF 60 DAYS

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

 

[vishal0589]

Idea is good. But make sure 4 P(Present) between two weekly offs (W/O).

 

[oldbrewer]

the pattern is designed for 4 minimum but if cl and el creep in I think manual intervention

otherwise a lot of time writing code to cover every eventuality....

 

[vishal0589]

Hi,
This process has to be automated one. Manual intervention is not required to complete this task.

 

[oldbrewer]

ok every time there are 4 P's, insert a W/O - could mean 6 W/O in 30 days

every time there are 5 P's insert a W/O - results in 4 or 5 W/O's

every time there are 6 P's max 4 W/O's but if cl and el could be only one or 2

I do not think as currently defined this can be automated - sorry

 

[vishal0589]

No problem bob,
I will try to automate the solution and hoping for other members from the board to help me out.