As a follow up question (successfully answered) regarding the calculation of our unique start date for each day (9am versus typical midnight) I need to calculate which of 4 crews were working for any given datetime. Our crews work a 4 on, 4 off, 12 hour shift (9am - 9pm, 9pm - 9am). In VBA the function to calculate which crew is this:
Function CrewCalcTime(TurnDate As Date) As String
'-------Function which returns Crew (A,B,C,D) based on a DateTime ------------------------------
Dim AdaysDiff As Integer, BdaysDiff As Integer, CdaysDiff As Integer, DdaysDiff As Integer
Dim Adays As Double, Bdays As Double, Cdays As Double, Ddays As Double
Dim r1 As Double, r2 As Double, CrewSel As String, Hours As Integer
Hours = Hour(TurnDate)
If Hours >= 0 And Hours < 9 Then
TurnDate = TurnDate - 1
r1 = 8
r2 = 11
ElseIf Hours >= 9 And Hours < 21 Then
r1 = 0
r2 = 3
ElseIf Hours >= 21 And Hours <= 23 Then
r1 = 8
r2 = 11
End If
AdaysDiff = DateDiff("d", #2/14/1996#, TurnDate)
BdaysDiff = DateDiff("d", #2/6/1996#, TurnDate)
CdaysDiff = DateDiff("d", #2/2/1996#, TurnDate)
DdaysDiff = DateDiff("d", #2/10/1996#, TurnDate)
'-------A Crew--------------
Adays = AdaysDiff / 16
AdaysDiff = Int(AdaysDiff / 16)
Adays = Adays - AdaysDiff
Adays = Adays * 16
'-------B Crew--------------
Bdays = BdaysDiff / 16
BdaysDiff = Int(BdaysDiff / 16)
Bdays = Bdays - BdaysDiff
Bdays = Bdays * 16
'-------C Crew--------------
Cdays = CdaysDiff / 16
CdaysDiff = Int(CdaysDiff / 16)
Cdays = Cdays - CdaysDiff
Cdays = Cdays * 16
'-------D Crew--------------
Ddays = DdaysDiff / 16
DdaysDiff = Int(DdaysDiff / 16)
Ddays = Ddays - DdaysDiff
Ddays = Ddays * 16
If Adays >= r1 And Adays <= r2 Then
CrewSel = "A"
ElseIf Bdays >= r1 And Bdays <= r2 Then
CrewSel = "B"
ElseIf Cdays >= r1 And Cdays <= r2 Then
CrewSel = "C"
ElseIf Ddays >= r1 And Ddays <= r2 Then
CrewSel = "D"
End If
CrewCalcTime = CrewSel
End Function
Is it possible make this part of an "autocalendar" calculation that will output a crew for any datetime field included in the load? For example, if I was loading a produced_date and a ship_date, I'd like to be able to output produced_crew and a ship_crew.
