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

[Ultramarp]

I have the following table x 600k rows

| Item ID |	Qty Picked |	Date Time	| Crew Name |
ABC123	4	7/17/2022 2:05:16 PM	?
123DEF	9	7/17/2022 7:05:16 PM	?
456XYZ	6	87/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.

[lbendlin]

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

[Ultramarp]

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]

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

No need to go deeper.