Forum Discussion

ddalton's avatar
ddalton
Icon for Resolver I rankResolver I
4 years ago
Solved

Grouping Data by Date Range and "Shift"

I have a dataset that includes a column with a date/time stamp. For the purposes of this exercise, that date/time stamp represents employee activity throughout their shift. Typically, there are three...
Need Help
  • ddalton's avatar
    ddalton
    4 years ago

    So far, in addition to the datetime column I had in the original post, I now have the following columns as I test possible solutions:

     

    TIME = VALUE(FORMAT([Created], "hh:mm:ss"))

SHIFT = SWITCH(true(), [TIME]>=0.25 && [TIME]<0.584, 1, [TIME]>=0.584 && [TIME]<19.2, 2, 3)

     

    I had issues with the SWITCH statement initially (a problem with text comparisons etc.) so converting the timestamp into a numeric value using the VALUE function allowed me to identify shifts, as above (where 1, 2 and 3 output values represent the different shifts).  

ddalton's avatar
ddalton
Icon for Resolver I rankResolver I
4 years ago

So far, in addition to the datetime column I had in the original post, I now have the following columns as I test possible solutions:

 

TIME = VALUE(FORMAT([Created], "hh:mm:ss"))

SHIFT = SWITCH(true(), [TIME]>=0.25 && [TIME]<0.584, 1, [TIME]>=0.584 && [TIME]<19.2, 2, 3)

 

I had issues with the SWITCH statement initially (a problem with text comparisons etc.) so converting the timestamp into a numeric value using the VALUE function allowed me to identify shifts, as above (where 1, 2 and 3 output values represent the different shifts).