# Calculating a "crew" from a pattern algorithm?

**Jordan Breiner**Oct 12, 2017 9:31 AM

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.