cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tybrneryan
Frequent Visitor

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

Accepted Solutions
Super User I
Super User I

Re: IF Statements For Date and Time

@Tybrneryan 

 

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

View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: IF Statements For Date and Time

@Tybrneryan 

 

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

Super User IV
Super User IV

Re: IF Statements For Date and Time

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Tybrneryan
Frequent Visitor

Re: IF Statements For Date and Time

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"

Super User I
Super User I

Re: IF Statements For Date and Time

@Tybrneryan 

 

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

View solution in original post

Tybrneryan
Frequent Visitor

Re: IF Statements For Date and Time

Thank you so much!!!

Highlighted
Tybrneryan
Frequent Visitor

Re: IF Statements For Date and Time

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

Super User I
Super User I

Re: IF Statements For Date and Time

@Tybrneryan 

 

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.

Tybrneryan
Frequent Visitor

Re: IF Statements For Date and Time

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"

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors