Forum Discussion

jhaldane's avatar
jhaldane
Frequent Visitor
8 years ago
Solved

Group Days and create shifts

Please can you assist me (step by step) Please can you show me how to group into days and then create three shifts. i.e. I have many rows for the 1st May all with different times. Then group times ...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi jhaldane,

     

    Month = Test_3[Dates].[MonthNo]

Day = Test_3[Dates].[Day] 

Time = FORMAT(Test_3[Dates] ,"hh:mm:ss")

Shift =
IF (
    Test_3[Time] >= TIME ( 6, 0, 0 )
        && Test_3[Time] < TIME ( 14, 0, 0 ),
    "Shift1",
    IF (
        Test_3[Time] >= TIME ( 14, 0, 0 )
            && Test_3[Time] < TIME ( 22, 0, 0 ),
        "Shift2",
        "Shift3"
    )
)

     

    Best regards,

    Yuliana Gu

jhaldane's avatar
jhaldane
Frequent Visitor
8 years ago

Thank you so much for responding.

 

Some questions on below.

 

I have managed to add the columns for month and time.

 

Please can you assist me with the column for 'shift'?

Where do i type in the formula for Shift?

What measure should the shift column be in?

 

 

Thanks

Jerry

 

 

 

Month = Test_3[Dates].[MonthNo]

Day = Test_3[Dates].[Day] 

Time = FORMAT(Test_3[Dates] ,"hh:mm:ss")

 

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi jhaldane,

 

For all above formulas in my original post, you should type them into calculated columns via clicking the highlighted button.

 

Create the calculated column: Month, Day and Time one by one. Then, remember to set the data type of [Time] field, as I mentioned in original post. After that, you can create the calculated column [Shift].

 

Regards,

Yuliana Gu