Forum Discussion
IF Statements For Date and Time
Anonymous
The syntax is for M query.
Open Edit queries, Add column->Custom Column -> Paste your script and rename the column.
If this helps, mark it as a solution
Kudos are nice too
Anonymous
Change the Date column from your source to Datetime datatype in Edit queries
Add a custom column as below. Adjust the shift timings and conditions accordingly.
=if Time.Hour([Column1])>=0 and Time.Hour([Column1])<11 then "A" else if Time.Hour([Column1]) >=12 and Time.Hour([Column1]) < 18 then "B" else "C"
I tested the above code with sample data and it works.
But do change the condition as per your need.
If this helps, mark it as a solution
I am doing somthing wrong. I am getting a syntax for 'Time' is incorrect error.
Here is the forumla with my column inserted:
Shift = if Time.Hour([AM_PRODUCTION_RESULT[END_DATE_TIME])>=0 and Time.Hour([AM_PRODUCTION_RESULT[END_DATE_TIME])<11 then "A" else if Time.Hour([AM_PRODUCTION_RESULT[END_DATE_TIME]) >=12 and Time.Hour([AM_PRODUCTION_RESULT[END_DATE_TIME]) < 18 then "B" else "C"
Anonymous
The syntax is for M query.
Open Edit queries, Add column->Custom Column -> Paste your script and rename the column.
If this helps, mark it as a solution
Kudos are nice too
Thank you so much!!!
This is also work on me however what if we consider the date since we have night shift.
Please see below expected output:
21/10/23 8:AM - 8PM is Shift A
21/10/23 8:PM to 22/10/23 8AM is Shift B
12/11/2023 8:01 A
12/11/2023 20:00 A
12/11/2023 20:01 B
13/11/2023 8:01 B
