Use time stamp to determine shift

[Opal55]

Hello all, 

I have a column with a date and time field formated as:  1/19/2022 2:05:16 PM

I need to calculate from this column whether it was Day Shift or Afternoon Shift.  Day shift starts at 7 AM Monday - Friday and afternoon shift starts at 5:30 PM M - Thur and 4:30 PM on Fridays.  Is there a way to do this? I have been looking but can't quite find a solution to my problem.  Any direction would be appreciated.

I thought that this might work:  

DvA = If(And(Tracking[Date/Time]>=TIME(07,00,00),Tracking[Date/Time]<=TIME(16,00,00)),"Day Shift","Afternoon Shift")

but my results are all Afternoon shift.... and that's not right.

[VahidDM · Accepted solution]

Hi @Opal55 

Try this code to add a new column:

DvA = 
Var _Time = TIMEVALUE(Tracking[Date/Tim])
Var _DN = WEEKDAY(Tracking[Date/Tim])
Var _MT = time(7,00,00)
Var _AF = if(_DN=6,time(16,30,00),time(17,30,00))
return
if(_Time>=_MT&&_Time<=_AF,"Day Shift","Afternoon Shift")

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Opal55]

Perfect!  Thank you so much for this!

[Daryl-Lynch-Bzy]

Hi @Opal55 - It looks like the Datetime to Time comparison does not work.  Time is treated as 1/1/1900 07:00:00.

please consider trying:

DvA = IF( AND( HOUR( Tracking[Date/Time] ) >= 7 , HOUR( Tracking[Date/Time] ) <= 16 ),"Day Shift","Afternoon Shift")

Or if Time is need, please split the Date Time into separate columns in Power Query instead.

Many thanks

Daryl

[Vera_33]

Hi @Opal55 

try

DvA = If(And(TIMEVALUE(Tracking[Date/Time])>=TIME(07,00,00),TIMEVALUE(Tracking[Date/Time])<=TIME(16,00,00)),"Day Shift","Afternoon Shift")