Grouping Data by Date Range and "Shift"

[ddalton]

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 shifts in a 24-hour period - though the shifts cover two days (i.e., 06:00 - 14:00, 14:00 - 22:00, 22:00 - 06:00). Sometimes the employee activity data might overlap shifts. 

I want to effectively group the rows in this table by date and shift and find a meaningful way to visualise the data in a graph. For example, having the date and shift on the x-axis (i.e., 01/08/2022 Shift 1, 01/08/2022 Shift 2, etc.) and then some other metric on the y-axis relevant to the specific employee activities. 

Assuming, for now, that the shifts are static (i.e., employee activity always sits within the specified shifts), what's the best way to achieve this? The method would need to determine the date of the activity and translate the timestamp into a shift. 

Should I create a calculated column (using a switch, perhaps) that checks the timestamp and assigns a number (i.e., if the timestamp is between 06:00 - 14:00 then assign value 1, if the timestamp is between 14:00 - 22:00 then assign value 2, etc.)? And collate that with a text string to bring the date and shift number into another column? 

Do I need to create a calculated columns at all? Is there a more efficient approach I could use instead? 

[v-xiaotang]

Hi @ddalton 

Thanks for reaching out to us.

Could you share some sample data? thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[ddalton · Accepted solution]

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]

I'm aware I could use the FORMAT function to grab the time from a datetime dataset etc. to create calculated columns though I'm unsure yet if I could use a BETWEEN operator for times inside a SWITCH statement.