Found Dates between Gaps

V

vishal0589

Board Regular
Joined
May 13, 2014
Messages
63
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.12345678910111213Output
1143PPPPW/OPPW/OPPPPW/O58
2499PW/OPPPPW/OPPPELELW/O713
3501PPPPCLPPPPW/OPPP
4505PW/OPPPPW/OCLPPPW/OP712
5
506PPCLPPW/OPELPPW/OPP611
6
509PW/OP
PPPW/OPPPPCLP
7758
PW/OPCLCLPW/OPPCLPW/OP2712

<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
 
Sort by date Sort by votes

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
S. No.Emp. No.W1W2W3W4W5W6W7W8W9W10W11W12W13Output13WEEK PERIOD
1143PPPPW/OPPW/OPPPPW/OW8
2499PW/OPPPPW/OPW/OPELELW/OW2W9W13
3501PPPPCLW/OPPPW/OPPPW10
4505PW/OPPPPW/OCLW/OPPW/OPW2W9W12
5506PPW/OPPW/OPELPPW/OPPW3W6
6509PW/OPPPPW/OPPPPCLPW2
7758PW/OPCLCLPW/OPW/OCLW/OW/OPW2W9W11W12
I added some more W/O 's for test purposes
the output table now shows the illegal W/O 's
this macro runs through the data and produces the output table
dd = 20
For k = 2 To 8
If Cells(k, 3) = "W/O" Then Cells(k, dd) = Cells(1, 3): dd = dd + 1
If Cells(k, 4) = "W/O" Then Cells(k, dd) = Cells(1, 4): dd = dd + 1
If Cells(k, 5) = "W/O" Then Cells(k, dd) = Cells(1, 5): dd = dd + 1
If Cells(k, 6) = "W/O" Then Cells(k, dd) = Cells(1, 6): dd = dd + 1
For j = 7 To 15
If Cells(k, j) = "W/O" Then GoTo 20 Else GoTo 40
20 If Cells(k, j - 1) = "W/O" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 2) = "W/O" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 3) = "W/O" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 4) = "W/O" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
40 Next j
dd = 20
50 Next k
End Sub

<tbody>
</tbody>
 
Upvote 0
Hi,
Thanks for your solution. But there is a problem in your solution.

Your code is considering gap between Weekly OFFs (W/O) only not Presents (P) between two Weekly Offs.

Suppose if there is any CL or EL or P is present between two W/O then your code is not considering presents (P).

Solution must check whether minimum 4 Presents (P) are available between two W/O or not.

Pls note: There may others details between two Weekly OFFs (W/O) viz, CL,EL,PL,etc.
 
Upvote 0
S. No.Emp. No.W1W2W3W4W5W6W7W8W9W10W11W12W13Output13WEEK PERIOD
1143PPPPW/OPPW/OPPPPW/OW8
2499PW/OPPPPW/OPW/OPELELW/OW2W9W13
3501PPPPCLW/OPPPW/OPPPW10
4505PW/OPPPPW/OCLW/OPPW/OPW2W9W12
5506PPW/OPPW/OPELPPW/OPPW3W6
6509PW/OPPPPW/OPPPPCLPW2
7758PW/OPCLCLPW/OPW/OCLW/OW/OPW2W9W11W12
I added some more W/O 's for teat purposes
the output table now shows the illegal W/O 's
this macro runs through the data and produces the output table
dd = 20
For k = 2 To 8
If Cells(k, 3) = "W/O" Then Cells(k, dd) = Cells(1, 3): dd = dd + 1
If Cells(k, 4) = "W/O" Then Cells(k, dd) = Cells(1, 4): dd = dd + 1
If Cells(k, 5) = "W/O" Then Cells(k, dd) = Cells(1, 5): dd = dd + 1
If Cells(k, 6) = "W/O" Then Cells(k, dd) = Cells(1, 6): dd = dd + 1
For j = 7 To 15
If Cells(k, j) = "W/O" Then GoTo 20 Else GoTo 40
20 If Cells(k, j - 1) = "W/O" Or Cells(k, j - 1) = "CL" Or Cells(k, j - 1) = "EL" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 2) = "W/O" Or Cells(k, j - 1) = "CL" Or Cells(k, j - 1) = "EL" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 3) = "W/O" Or Cells(k, j - 1) = "CL" Or Cells(k, j - 1) = "EL" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
If Cells(k, j - 4) = "W/O" Or Cells(k, j - 1) = "CL" Or Cells(k, j - 1) = "EL" Then Cells(k, dd) = Cells(1, j): dd = dd + 1: GoTo 40
40 Next j
dd = 20
50 Next k
End Sub
this can be added to if there are more codes
I did not run it but cant see y it wd not work

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
S. No.Emp. No.W1W2W3W4W5W6W7W8W9W10W11W12W1313WEEK PERIOD
1143PPW/OPPPPW/OPPW/OPW/OW3W11W13
2499PW/OPPPPW/OPW/OPELELW/OW2W9W13
3501PPPPCLW/OPPPW/OPPPW10
4505PW/OPPPPW/OCLW/OPPW/OPW2W9W12
5506PPW/OPPW/OPELPPW/OPPW3W6W11
6509PW/OPPPPW/OPPPPCLPW2
7758PW/OPCLCLPW/OPW/OCLW/OW/OPW2W7W9W11W12
this macro runs through the data and produces the output table
dd = 20
For k = 2 To 8
For j = 3 To 15
If Cells(k, j) = "P" Then pp = pp + 1: GoTo 100
If Cells(k, j) = "W/O" Then GoTo 20 Else GoTo 100
20 If pp > 3 Then GoTo 50
30 dd = dd + 1: Cells(k, dd) = Cells(1, j)
50 pp = 0
100 Next j
dd = 20: pp = 0
Next k
End Sub

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Thanks dear, Its perfect.

This is what I was looking for.

Further, If you don't mind can I ask you another query in this regard?


Like this rule, now I want to assign Weekly Offs (W/O) in attendance register to every employees.

e.g:

Minimum 3 Weekly offs (Max 4) should be given to each employee provided he has worked for atleast 4 P (Present).


Need a macro to do this dynamically in same dataset.

Hope you understand.
 
Upvote 0
Hi,
Max 4 Weekly offs (Minimum 3) should be assigned to each employee with same condition i.e. there must be 4 Presents (P) between 2 Weekly Offs (W/O):

Suppose 4 weekly offs to be given within a month with the assumption of 30 Days.

These 4 Weekly Offs could be a randomised one. Different weekly offs could be assigned to employees.

E.g:

S. No.Emp. No.123456789101112131415161718192021222324252627282930
1143PPPPCLPPCLPPPPCLPPPPCLELPPELELCLPPPPPP
2499PCLPPPPCLPPPELELCLPPPELPPPPELPPPCLPPELP
3501PPPPCLPPPPCLPPPPELPPELELELPPPPELPPPPP
4505PELPPPPCLCLPPPCLPPPPPPPELPELPPPPPPPP
5506PPCLPPCLPELPPCLPPPPPPPPPELPPPPELPPPP
6509PCLPPPPCLPPPPCLPPPPELPPPELELPPPPAPPP
7758PCLPCLCLPCLPPCLPCLPPPPELPPPPELPPPPCLPPP

<tbody>
</tbody>

should be assigned as:

S. No.Emp. No.123456789101112131415161718192021222324252627282930
1143PPPPCLPW/OCLPPPPCLPPW/OPCLELPPELELCLPW/OPPPP
2499PCLPPPW/OCLPPPELELCLPW/OPELPPPW/OELPPPCLPW/OELP
3501PPPPCLPW/OPPCLPPW/OPELPPELELELPPW/OPELPPPPP
4505PELPPPW/OCLCLPPPCLPPW/OPPPPELPELPW/OPPPPPP

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Circular reference?
Replies
6
Views
228
RoryA
RoryA
U
  • Solved
Multi-tab excel, transporting data across tabs to a summary tab
Replies
3
Views
111
awoohaw
awoohaw
N
  • Question
MATCH two ranges, column by column
Replies
4
Views
194
Fluff
Fluff
MrDB4Excel
  • Solved
Get All Instances of a Specific Value
Replies
6
Views
555
KRice
K
ibmy
  • Solved
Excel file freezes and stop working, file size 1GB
Replies
5
Views
158
ibmy
ibmy

Forum statistics

Threads
1,215,480
Messages
6,125,047
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top