I have times in one column all “22:00” in “HH:MM” format. I run a function to get the shift pattern, which changes at 22:00.

“2” is late shift – up to 22:00
“3” is night shift – after 22:00.

I just wonder why the same times would give a different result.

This is the function. I know 3 and 2 have an “=” which means it could fit both criteria but if i put one as “>=” and the other as “<” off one of the time it doesn’t change the output. I have calcswitched on.

Public Function GetShift(TimeIn As Date) As Long
  GetShift = Switch(TimeIn >= #10:00:00 PM#, 3, _
                TimeIn <= #6:00:00 AM#, 4, _
                TimeIn >= #6:00:00 AM# And TimeIn <= #2:00:00 PM#, 1, _
                TimeIn >= #2:00:00 PM# And TimeIn <= #10:00:00 PM#, 2 _
                )
End Function

This is the formula to run the function. I have no problem with this. It works perfectly

Sub gEtshiftformula()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.CutCopyMode = False
Application.DisplayAlerts = False

  On Error Resume Next

   Dim r As Range, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("E2:E" & lr).Cells
r.Formula = "=getshift(RC[2])"
End Sub