Forum Discussion

Ultramarp's avatar
Ultramarp
Frequent Visitor
4 years ago

How do i create a column with the Crew name based on Date_Time ?

I have the following table x 600k rows

 

| Item ID |Qty Picked |Date Time | Crew Name |
ABC12347/17/2022 2:05:16 PM ?
123DEF97/17/2022 7:05:16 PM?
456XYZ687/18/2022 2:05:16 AM?

 

I have 3 crews

Example: Today is July 15th
Crew 2 is Working on day shift

Crew 3 is Working on night shift

Crew 1 Rests while crew 2 and 3 work,  

 

Day Shift - It`s a 12 hour shift Starting on Tuesday 6 AM finishing next monday at 6 PM.

Night Shift - It`s a 12 hour shift Starting on Friday 6 PM finishing next Friday at 6 AM.

 

While this happens the third Crew is resting From tuesday till friday and the cycle starts again.

 

My goal is to get KPIs like which crew processed more transactions, peak times, etc..

 

I have already solved whether is a "Night" shift or a "Day" shift with this code i found on this post, but i can`t figure out how to apply it to the crew name situation.

Shift =
Var _Time = TIMEVALUE(sheet1[Dt Tmstamp])
Var _DN = WEEKDAY(sheet1[Dt Tmstamp])
Var _MT = TIME(6,00,00)
Var _NF = IF(_DN=6,time(18,00,00),time(17,59,00))
return
IF(_Time>=_MT && _Time<=_NF ,"Day" , "Night")
 
I`d appreciate any help, thank you.
 

 

 
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Use an external dates/calendar table that includes your crew schedules.  Looks like that table needs to be set at half day granularity.

    • Ultramarp's avatar
      Ultramarp
      Frequent Visitor
      4 years ago

      Thank you for your response.

      I already have a DATE table on my schema, i would need to create a new one right?

      So it would be like...Year -> Month -> Day -> Half day -> Hour -> Minute -> Second, a column for each one

       

      i would have to add the crew name to the date manually then, right?

      it is complicated to get it add it dynamically.

       

      Thanks.

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        it would be like...Year -> Month -> Day -> Half day

        No need to go deeper.