Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

@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

Connect on LinkedIn

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

Refer

https://community.powerbi.com/t5/Desktop/Comparing-Time-in-PowerBI-and-Creating-a-new-column/td-p/13...

https://community.powerbi.com/t5/Desktop/How-to-compare-a-time-from-transaction-table-with-shift-tab...

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
VasTg
Memorable Member
Memorable Member

@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.

 

Cap11.PNG

 

But do change the condition as per your need.

If this helps, mark it as a solution

Connect on LinkedIn
Julz
Regular Visitor

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



Anonymous
Not applicable

I am doing somthing wrong. I am getting a syntax for 'Time' is incorrect error.

 

image.pngimage.png

 

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

Connect on LinkedIn

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!

Anonymous
Not applicable

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.

Connect on LinkedIn
Anonymous
Not applicable

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"

Anonymous
Not applicable

Thank you so much!!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.