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

    Calculating a "crew" from a pattern algorithm?

    Jordan Breiner

      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.