M code problem, create column for shift pattern based on parameterised times.

[Anonymous]

I have this m code for a column:
if Time.From([#"Date/Time"]) >= Time.From(#"Shift Pattern 1 Start") and Time.From([#"Date/Time"]) <= Time.From(#"Shift Pattern 1 End") then "Shift Pattern 1"
else if Time.From([#"Date/Time"]) >= Time.From(#"Shift Pattern 2 Start") and Time.From([#"Date/Time"]) <= Time.From(#"Shift Pattern 2 End") then "Shift Pattern 2"
else if Time.From([#"Date/Time"]) >= Time.From(#"Shift Pattern 3 Start") and Time.From([#"Date/Time"]) <= Time.From(#"Shift Pattern 3 End") then "Shift Pattern 3"
else null

The times rows that are within shift pattern 1 correctly display "shift pattern 1" in the column, but any other condition returns null. What is the problem with this code?

Any additional info need please comment and i'll get back to you straight away. Thanks!

[j_ocean]

If you're using Time.From I take it things aren't being stored in time or datetime formats, correct? Are you sure shift patterns 2+ are parsing as times correctly?

I'd suggest converting the columns to a consistent format in an earlier step and visually confirming how it works. Side benefit: this step will have simpler code.

[mlsx4]

Hi @Anonymous 

Have you checked the conditions one by one? I mean just leave this part and check:

if Time.From([#"Date/Time"]) >= Time.From(#"Shift Pattern 2 Start") and Time.From([#"Date/Time"]) <= Time.From(#"Shift Pattern 2 End") then "Shift Pattern 2" else null

It would also be very useful if you attached some example data of your table (with dummy data)