Forum Discussion
IF Statements For Date and Time
Hello,
I am trying to create an IF statement by date and time to associate a shift letter to(A.B,C,D).
I have an end date column pulling from a table in this format: 10/21/2019 2:00:00 PM.
I would like to assign a shift to this date and time column.
If B shift is 12:00 PM to 6:00 PM, then the example above would be assigned B shift.
I am connected via ODBC and using Power BI.
Any help would be much appreciated.
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
10 Replies
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
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
Refer
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
