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
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!!!
Going to add another level of complexity here, How about bringing in Days of the week?
Example:
Sunday through Tuesday 6:00 am-6:00 pm and Wednesday 6:00 am-Noon = A shift
Anonymous
Add "Date.DayOfWeek([Column1])" to the condition. It will return 0 for Monday,1 for Tuesday and so on..
If this helps, mark it as a solution.
Kudos are nice too.
How to I insert it?
f Time.Hour([END_DATE_TIME])>=7 and Time.Hour([END_DATE_TIME])<16 then "A" else if Time.Hour([END_DATE_TIME]) >=15 and Time.Hour([END_DATE_TIME]) < 23 then "B" else "C"
Hi,
Thanks for your solution, it really helped us for the shifts. Only we have a problem that the night shift starts at 22:00 till 6:00 a.m. next day.
The problem for now is that for example 2-2-2023 22:00 - 00:00 is summed up with the night shift ealier that day form 2-2-2023 from 00:00 - 6:00, which is another night shift.
So what the output should be is that the night shift, started at 1-2-2023 22:00 till 2-2-2023 6:00 should be summed up. And it should be categorized as the night shift of 1-2-2023 (the start date of the night shift).
So what we have now is this;
if Time.Hour([Start])>=6 and Time.Hour([Start])<14 then "Day"
else if Time.Hour([Start]) >=14 and Time.Hour([Start]) < 22 then "Evening"
else "Nacht")So the last else should be something like;
else if Time.Hour([Start]) >=22 and Time.Hour([Start]) +1 day < 6 then "Night"
Can you push us in the right direction?
Thanks in advance!