Since June Col A has Excel-friendly dates, you could simplify and get rid of Cols E, J, and K
Here's a function Shift1 that seems to work
Option Explicit
Function Shift1(T As Date, DayShift As Boolean) As Long
Select Case T - Int(T)
Case TimeSerial(6, 0, 0) To TimeSerial(22, 59, 0)
Shift1 = IIf(DayShift, 1, 0)
Case TimeSerial(23, 0, 0) To TimeSerial(23, 59, 0)
Shift1 = IIf(DayShift, 0, 1)
Case TimeSerial(0, 0, 0) To TimeSerial(6, 0, 0)
Shift1 = IIf(DayShift, 0, 1)
End Select
End Function
Capture.JPG
Personally, I'd make a macro to reformat May worksheet's Col A into Excel-friendly dates and use the same logic on all sheets
' 1111111
'1234567890123456
'05/01/2021 07:05 2021-05-01 07:05:13.0
Sub FixOldData()
Dim r As Range, r1 As Range
Dim i As Long, y As Long, m As Long, d As Long, h As Long, n As Long
With ActiveSheet
Set r = .Cells(2, 1)
Set r = Range(r, r.End(xlDown))
End With
For Each r1 In r.Cells
With r1
y = Mid(.Value, 7, 4)
m = Left(.Value, 2)
d = Mid(.Value, 4, 2)
h = Mid(.Value, 12, 2)
n = Mid(.Value, 15, 2)
.Value = DateSerial(y, m, d) + TimeSerial(h, n, 0)
End With
Next
End Sub