Scanning schedule

shekhar_pc · Dec 2, 2006

try.xls

A

B

C

D

E

F

G

H

I

J

1

Employee

Emp ID

2-Dec

3-Dec

4-Dec

5-Dec

6-Dec

7-Dec

8-Dec

9-Dec

2

A

1234

09:00 PM - 06:00 AM

3

B

1235

11:30 PM - 08:30 AM

4

C

1236

02:30 AM - 11:30 AM

Casual Leave

02:30 AM - 11:30 AM

5

D

1237

05:30 AM - 03:00 PM

6

E

1238

11:30 PM - 08:30 AM

7

F

1239

05:30 AM - 03:00 PM

Sick Leave

05:30 AM - 03:00 PM

8

G

1240

09:00 PM - 06:00 AM

9

H

1241

02:30 AM - 11:30 AM

Sheet1

Hi!

I have posted this question earlier but did not get any help. I have tried a lot but I am unable to achieve what I need.

The table posted above is the schedule for my employee. I need an input box which asks for a date. When the user supplies date, it should look for people who are scheduled for that particular date and who are on leave and put the result in the next worksheet at the last occupied row.

For instance, if I supply the date as 2nd Dec then the next worksheet should be filled with @ row # 2 (Row1 has headings)
Employee Emp ID Schedule
A 1234 09:00 PM - 06:00 AM
B 1235 11:30 PM - 08:30 AM
C 1236 02:30 AM - 11:30 AM
E 1238 11:30 PM - 08:30 AM
G 1240 09:00 PM - 06:00 AM

Then if I select 3rd Dec, the next worksheet should be filled with the following data starting fom row number 7 (just below the above data)
A 1234 09:00 PM - 06:00 AM
B 1235 11:30 PM - 08:30 AM
D 1237 05:30 AM - 03:00 PM
G 1240 09:00 PM - 06:00 AM

shekhar_pc · Dec 2, 2006

The problem here is that if the start time is "PM" then it should look at the same date supplied but if the start time is "AM", it should look for the next date of the date supplied.

For example. If the date supplied is 4th Dec then:
1. "A" is working for 09:00 PM - 06:00 AM shift the start time is 9PM, then take him.
2. "B" is not working on 4th, still check the schedule on 5th (if there is an AM shift), there isn't so ignore this person.
3. "C" is having a leave.
4. "D's" start shift is "AM" so look for the next day and put that shift accordingly.
5. "E" is not showing up on 4th, still check the schedule on 5th (if there is an AM shift), there isn't so ignore this person.
6. "F" is not showing up on 4th, still check the schedule on 5th (if there is an AM shift), there is so take this shift.
7. "G" is working for 09:00 PM - 06:00 AM shift the start time is 9PM, then take him.
8. "H" is not showing up on 4th, still check the schedule on 5th (if there is an AM shift), there is so take this shift.

Hence on 4th the schedule should be:
A 1234 09:00 PM - 06:00 AM
C 1236 Casual Leave
D 1237 05:30 AM - 03:00 PM
G 1240 09:00 PM - 06:00 AM
H 1241 02:30 AM - 11:30 AM

5th
C 1236 02:30 AM - 11:30 AM
D 1237 05:30 AM - 03:00 PM
E 1238 11:30 PM - 08:30 AM
G 1240 09:00 PM - 06:00 AM
H 1241 02:30 AM - 11:30 AM

6th
B 1235 11:30 PM - 08:30 AM
C 1236 02:30 AM - 11:30 AM
D 1237 05:30 AM - 03:00 PM
E 1238 11:30 PM - 08:30 AM
F 1239 Sick Leave
G 1240 09:00 PM - 06:00 AM
H 1241 02:30 AM - 11:30 AM

7th
A 1234 09:00 PM - 06:00 AM
B 1235 11:30 PM - 08:30 AM
D 1237 05:30 AM - 03:00 PM
E 1238 11:30 PM - 08:30 AM
F 1239 05:30 AM - 03:00 PM
H 1241 02:30 AM - 11:30 AM

8th
A 1234 09:00 PM - 06:00 AM
B 1235 11:30 PM - 08:30 AM
E 1238 11:30 PM - 08:30 AM
F 1239 05:30 AM - 03:00 PM
H 1241 02:30 AM - 11:30 AM

shekhar_pc · Dec 2, 2006

shekhar_pc · Dec 3, 2006

Please help.

Krishnakumar · Dec 3, 2006

Hi,

Code:

Sub TestIt()
Dim ws1     As Worksheet, ws2   As Worksheet
Dim lRow    As Long, DtCol      As Byte
Dim SchdDt  As String, SchCell    As Range
Dim Rng     As Range, SchdTime  As String
Dim Dest    As Range, x

Set ws1 = Sheets("Sheet1"): Set ws2 = Sheets("Sheet2")
lRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set Dest = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
SchdDt = Application.InputBox("Enter Date", "Schedule Date", "m/d/yyyy")
    Application.ScreenUpdating = False
    If SchdDt = vbNullString Then
        Exit Sub
    End If
    SchdDt = Format(SchdDt, "d-mmm")
    If Application.WorksheetFunction.CountIf(ws1.Rows(1), SchdDt) > 0 Then
    DtCol = ws1.Rows(1).Find(What:=SchdDt, LookIn:=xlValues).Column
        Set Rng = ws1.Range(ws1.Cells(2, DtCol), ws1.Cells(lRow, DtCol))
            For Each SchCell In Rng
                If Not IsEmpty(SchCell) Then
                    x = Split(SchCell.Value, "-")
                    SchdTime = x(0)
                    If InStr(1, SchdTime, "PM") > 0 Or InStr(1, SchCell, "Leave") Then
                        Dest = ws1.Cells(SchCell.Row, 1)
                        Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                        Dest.Offset(, 2) = SchCell
                        Set Dest = Dest.Offset(1)
                    Else
                        If Not IsEmpty(SchCell.Offset(, 1)) Then
                        x = Split(SchCell.Offset(, 1).Value, "-")
                        SchdTime = x(0)
                            If InStr(1, SchdTime, "AM") > 0 Then
                                Dest = ws1.Cells(SchCell.Row, 1)
                                Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                                Dest.Offset(, 2) = SchCell.Offset(, 1)
                                Set Dest = Dest.Offset(1)
                            End If
                        End If
                    End If
                Else
                    If Not IsEmpty(SchCell.Offset(, 1)) Then
                        x = Split(SchCell.Offset(, 1).Value, "-")
                        SchdTime = x(0)
                            If InStr(1, SchdTime, "AM") > 0 Then
                                Dest = ws1.Cells(SchCell.Row, 1)
                                Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                                Dest.Offset(, 2) = SchCell.Offset(, 1)
                                Set Dest = Dest.Offset(1)
                            Else
                                GoTo Again
                            End If
                    Else
                        GoTo Again
                    End If
                End If
Again:
            Next SchCell
    Else
        MsgBox "Date: '" & SchdDt & "' Not Found!"
    End If
    Application.ScreenUpdating = True
End Sub

HTH

shekhar_pc · Dec 4, 2006

Super !!!

I really have to sit down for few hours to understand each and every aspect of this code. Thank you very much.

One more thing. In fact I should be sorry for not explaining one small portion of the problem.

The entire data is correct except the leaves. It is not your mistake, I did not explain it properly and my example data was also incorrect.

I would be very thankful if you can correct this part too.

The casual leave for Employee "C" on 4th Dec should be reflected in 3rd Dec because he is doing a AM shift throughout the week.

Similarly the Sick leave for Employee "F" on 6th Dec should be reflected in 5th Dec because he is doing a AM shift throughout the week.

If the shift start time starts in PM only then consider that leave in the same day

Thank you
Shek

Krishnakumar · Dec 4, 2006

Hi,

Code:

Sub SchedScan_v01()
Dim ws1     As Worksheet, ws2   As Worksheet
Dim lRow    As Long, DtCol      As Byte
Dim SchdDt  As String, SchCell    As Range
Dim Rng     As Range, SchdTime  As String
Dim Dest    As Range, x

Set ws1 = Sheets("Sheet1"): Set ws2 = Sheets("Sheet2")
lRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set Dest = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
SchdDt = Application.InputBox("Enter Date", "Schedule Date", "m/d/yyyy")
    Application.ScreenUpdating = False
    If SchdDt = vbNullString Then
        Exit Sub
    End If
    SchdDt = Format(SchdDt, "d-mmm")
    If Application.WorksheetFunction.CountIf(ws1.Rows(1), SchdDt) > 0 Then
    DtCol = ws1.Rows(1).Find(What:=SchdDt, LookIn:=xlValues).Column
        Set Rng = ws1.Range(ws1.Cells(2, DtCol), ws1.Cells(lRow, DtCol))
            For Each SchCell In Rng
                If Not IsEmpty(SchCell) Then
                    x = Split(SchCell.Value, "-")
                    SchdTime = x(0)
                    If InStr(1, SchdTime, "PM") > 0 Then
                        Dest = ws1.Cells(SchCell.Row, 1)
                        Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                        Dest.Offset(, 2) = SchCell
                        Set Dest = Dest.Offset(1)
                    Else
                        If InStr(1, SchdTime, "AM") > 0 Then
                            If Not IsEmpty(SchCell.Offset(, 1)) Then
                                If InStr(1, SchCell.Offset(, 1), "Leave") > 0 Then
                                    Dest = ws1.Cells(SchCell.Row, 1)
                                    Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                                    Dest.Offset(, 2) = SchCell.Offset(, 1)
                                    Set Dest = Dest.Offset(1)
                                End If
                            End If
                        End If
                    End If
                Else
                    If Not IsEmpty(SchCell.Offset(, 1)) Then
                        x = Split(SchCell.Offset(, 1).Value, "-")
                        SchdTime = x(0)
                            If InStr(1, SchdTime, "AM") > 0 Then
                                Dest = ws1.Cells(SchCell.Row, 1)
                                Dest.Offset(, 1) = ws1.Cells(SchCell.Row, 2)
                                Dest.Offset(, 2) = SchCell.Offset(, 1)
                                Set Dest = Dest.Offset(1)
                            Else
                                GoTo Again
                            End If
                    Else
                        GoTo Again
                    End If
                End If
Again:
            Next SchCell
    Else
        MsgBox "Date: '" & SchdDt & "' Not Found!"
    End If
    Application.ScreenUpdating = True
End Sub

shekhar_pc · Dec 4, 2006

Employee Emp ID 3-Dec
A 1234 Casual Leave
B 1235 11:30 PM - 08:30 AM
C 1236 Casual Leave
D 1237
E 1238 Sick Leave
F 1239
G 1240 09:00 PM - 06:00 AM
H 1241

I have changed the schedule for 3-Dec to above. In this case, the extracted schedule should be the below if I select 3rd Dec as the date:

A 1234 Casual Leave
B 1235 11:30 PM - 08:30 AM
C 1236 Casual Leave
D 1237 05:30 AM - 03:00 PM
E 1238 Sick Leave
G 1240 09:00 PM - 06:00 AM

Because

"A" is in 9:30 shift (start time starts in PM)
"B" is scheduled for 11:30 shift (start time starts in PM)
"C" is in 2:30 shift (start time starts in AM) therefore his leave on 4th Dec is actually for 3rd.
"D" is working for 5:30 shift (start time starts in AM)
"E" is in 11:30 shift (start time starts in PM)
"G" is scheduled for 9:30 shift (start time starts in PM)

shekhar_pc · Dec 4, 2006

Your logic is working. I also want the Leaves to be detected but it is not being detected in the revised 3rd Dec scheduled that I have changed.

You will have to check the entire week's schedule for each employee. If the shift start time is in "PM" then the leave will be for the same day and if it starts with "AM", the leave should be detected in the previous day.

I am not sure if I am explaining my problem properly. I hope you have understood my concern

Krishnakumar · Dec 4, 2006

Hi,

Replace

Code:

If InStr(1, SchdTime, "PM") > 0 Then

with

Code:

If InStr(1, SchdTime, "PM") > 0 Or InStr(1, SchCell.Value, "Leave") > 0 Then

HTH

Scanning schedule

shekhar_pc

Board Regular

Excel Facts

shekhar_pc

Board Regular

shekhar_pc

Board Regular

shekhar_pc

Board Regular

Krishnakumar

Well-known Member

shekhar_pc

Board Regular

Krishnakumar

Well-known Member

shekhar_pc

Board Regular

shekhar_pc

Board Regular

Krishnakumar

Well-known Member

Similar threads

Forum statistics

Share this page

Scanning schedule

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock