Dealing with Midnight TimeValue

[amaglam]

Hello, I am creating a tool where the user will running it for Day Shift and Night Shift about 15 minutes after the hour. The logic for Day Shift is fine, however, I just ran it after midnight and the date showed as todays date, as you would presume. The issue is that if a Night Shift manager wants to run it at 01:00, but they want to see data from the start of shift(19:00) then the date should be todays (date - 1) and after the column becomes >= 00:00 the start date should be todays date. My column headers are time value going from 17:00 to 05:00. Any help would be greatly appreciated.

             Select Case intStartHour
                Case 19 To 22
                    If intCurrentHour < 7 Then
                        strStartDate = Format(Now - 1, "YYYY-MM-DD")
                        strEndDate = Format(Now - 1, "YYYY-MM-DD")
                    Else
                        strStartDate = Format(Now, "YYYY-MM-DD")
                        strEndDate = Format(Now, "YYYY-MM-DD")
                    End If
                Case 23
                    If intCurrentHour < 7 Then
                        strStartDate = Format(Now - 1, "YYYY-MM-DD")
                        strEndDate = Format(Now, "YYYY-MM-DD")
                    Else
                        strStartDate = Format(Now, "YYYY-MM-DD")
                        strEndDate = Format(Now + 1, "YYYY-MM-DD")
                    End If
                Case Else
                    strStartDate = Format(Now, "YYYY-MM-DD")
                    strEndDate = Format(Now, "YYYY-MM-DD")
                End Select

 

[jasonb75]

The issue is that if a Night Shift manager wants to run it at 01:00, but they want to see data from the start of shift(19:00)

The wording of that sentence implies that they could want to see data from the start of the shift, or from the middle.
Perhaps a Yes/No message box asking them if they want to run from start of shift for the time window where that could happen?

 

[mikerickson]

It sounds like

If (Now - Int(Now)) < TimeValue ("05:00:00") Then
    StartDate = Date - 1
Else
    StartDate = Date
End If