View Full Version : Automatically determines what shift is working based on time

10-18-2016, 10:28 PM
Hey guys,

I was wondering if there is a code that will automatically determine what the current shift is and fill that information in a cell in excel? So third shift starts at 10PM, First shift Starts at 6AM and second shift starts at 2PM. We have a log book that gets filled out once per shift. Is there anyway to have it automatically know what shift is working based on the actual time and put the shift in cell G1 in the excel workbook?

10-18-2016, 11:00 PM
Hi there,

You could use an If...Then...ElseIf...Else statement or Switch or Select Case. How does the code know when to run?

Option Explicit

Sub test()
MsgBox RetShift(#1:55:00 PM#)
End Sub

Function RetShift(TimeIn As Date) As Long
RetShift = Switch(TimeIn >= #10:00:00 PM#, 3, _
TimeIn <= #5:59:59 AM#, 3, _
TimeIn >= #6:00:00 AM# And TimeIn <= #2:00:00 PM#, 1, _
TimeIn >= #2:00:00 PM# And TimeIn <= #9:59:59 PM#, 2 _
End Function

10-18-2016, 11:15 PM
Thank you

10-18-2016, 11:27 PM
Log Sheet Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shift As String

Select Case CInt(Format(Time, "hh"))
Case 0 To 5: Shift = "Third"
Case 6 To 13: Shift = "First"
Case 14 To 21: Shift = "Second"
Case 22 To 23: Shift = "Third"
End Select

Range("G1") = Shift
End Sub