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 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.
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/

     

     

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    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

      Perfect!  Thank you so much for this!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

     

    try

    DvA = If(And(TIMEVALUE(Tracking[Date/Time])>=TIME(07,00,00),TIMEVALUE(Tracking[Date/Time])<=TIME(16,00,00)),"Day Shift","Afternoon Shift")
  • 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