1 Reply Latest reply: Oct 13, 2017 9:42 AM by Jordan Breiner

Calculating a "crew" from a pattern algorithm?

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

BdaysDiff = DateDiff("d", #2/6/1996#, TurnDate)

CdaysDiff = DateDiff("d", #2/2/1996#, TurnDate)

DdaysDiff = DateDiff("d", #2/10/1996#, TurnDate)

'-------A Crew--------------

'-------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

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.

• Re: Calculating a "crew" from a pattern algorithm?

Would it make more sense to create a table of dates with two dates per day (start of AM shift, start of PM shift) and a 3rd field which represents the crew?  I guess I could do this quickly in Excel and load it, but would like this to be available to everyone on the enterprise server.

Thanks,

Jordan