Struggling with logic in VBA: includes Date & TIme

Question

I am creating a program to display previous, 1 shift data: There are total of 3 shifts: Shift 1: 6 am to 2 pm Shift 2: 2 pm to 10 pm Shift 3: 10 pm to 6 am So depending upon current time, I should be able to copy all the data from just the previous 1 shift.

I have been wrecking my brains on this for the past week, no luck till now, the program runs fine but the logic isn't right I feel. I am getting other data too, that doesn't belong to the previous shift.

Here is the extract that includes only the logic: Would appreciate any help on this!

For i = lastrow1 To (lastrow1 - 150) Step -1
        mydate = Sheets("Summary").Cells(i, "A").Value
        mytime = Sheets("Summary").Cells(i, "B").Value
        mystatus = Sheets("Summary").Cells(i, "J").Value
        Sheets("Previousshiftdata").Activate
        lastrow2 = Sheets("Previousshiftdata").Range("A" & Rows.Count).End(xlUp).Row
    'j indicates destination row no i.e. row no. in previoushift
        j = lastrow2 + 1
    'to get shift 3 data
        If (x = "Shift 3" And (mydate = Date - 1 And mytime > TimeValue("22:00:00"))) Or (mydate = Date And mytime > TimeValue("00:00:00") And mytime < TimeValue("6:00:00")) Then
            Sheets("Summary").Activate
            Sheets("Summary").Range(Cells(i, "A"), Cells(i, "J")).Copy
            Sheets("Previousshiftdata").Activate
            Sheets("Previousshiftdata").Range(Cells(j, "A"), Cells(j, "J")).Select
            Selection.PasteSpecial Paste:=xlPasteValues
    'to get shift 2 data
        ElseIf ((mydate = Date) And (mytime > TimeValue("14:00:00")) And (mytime < TimeValue("22:00:00"))) Or ((mydate = Date - 1) And (mytime > TimeValue("14:00:00")) And (mytime < TimeValue("22:00:00"))) Then
            Sheets("Summary").Activate
            Sheets("Summary").Range(Cells(i, "A"), Cells(i, "J")).Copy
            Sheets("Previousshiftdata").Activate
            Sheets("Previousshiftdata").Range(Cells(j, "A"), Cells(j, "J")).Select
            Selection.PasteSpecial Paste:=xlPasteValues
    'to get shift 1 data
        ElseIf (TimeValue("6:00:00") < mytime < TimeValue("14:00:00")) And (mydate = Date) Then
            Sheets("Summary").Activate
            Sheets("Summary").Range(Cells(i, "A"), Cells(i, "J")).Copy
            Sheets("Previousshiftdata").Activate
            Sheets("Previousshiftdata").Range(Cells(j, "A"), Cells(j, "J")).Select
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
 'to clear clipboard
        Application.CutCopyMode = False
    Next i
    Sheets("Previousstatus").Activate
    End Sub

Answer 1

TimeValue("6:00:00") < mytime < TimeValue("14:00:00")

VBA doesn't handle this like you're expecting it to. If you wanted to do something like that, then you'd need to do:

TimeValue("6:00:00") < mytime AND mytime < TimeValue("14:00:00")

My recommendation to you would be to make a little function that checks if a given time is between two other times:

Private Function isTimeBetween(timeToCheck As Date, shiftStartTime As String, shiftEndTime As String)
    Dim shiftStartTime2 As Date
    Dim shiftEndTime2 As Date

    shiftStartTime2 = TimeValue(shiftStartTime)
    shiftEndTime2 = TimeValue(shiftEndTime)

    If shiftStartTime2 < timeToCheck And timeToCheck < shiftEndTime2 Then
        isTimeBetween = True
    Else
        isTimeBetween = False
    End If
End Function

Use like so:

Dim mytime As Date
mytime = TimeValue("10:00:00")
isTimeBetween(mytime, "6:00:00", "14:00:00")

...which returns True. It will clean up your code a lot to break it up into smaller functions.