Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Use time stamp to determine shift

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...
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    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/

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous - 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