Forum Discussion
Simple Workday/OffHours custom column
I am trying to make a custom column that shows either "Workday" or "Off Hours".
I have a time type column called "TimeCreated". It is in AM/PM not 24-hour.
My conditions are as follows, but no matter how I try to "phrase" this, I get syntax errors.
Day/Night = If ([TimeCreated] >= #time(8,0,0) and [TimeCreated] < #time(17,0,0) then "Workday"
Else "Off Hours"
Can I compute using (17,0,0) or 17:00:00 when I am not using a 24 hour time? Is that my problem?
If not, what is the best way to convert this to the 24 hour clock?
I have read similar queries here on this topic, but all more complicated than mine. I'm having difficulty applying the solutions to my own situation.
Please let me know if I should be providing more information. I'm somewhat new to PowerBI, so usually assume my situation is fairly simple.
Thanks,
Sarah
Hi SarahHope, lbendlin, thank you for your prompt reply!
We can use the Hour function to return the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.):
Create a calcualted column as shown below:
DayNight = IF( HOUR('Table'[TimeCreated]) >= 8 && HOUR('Table'[TimeCreated]) < 17, "Workday", "Off Hours" )Reference link:
HOUR function (DAX) - DAX | Microsoft Learn
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
there is no "time type column". It's a datetime column formatted as time.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi SarahHope, lbendlin, thank you for your prompt reply!
We can use the Hour function to return the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.):
Create a calcualted column as shown below:
DayNight = IF( HOUR('Table'[TimeCreated]) >= 8 && HOUR('Table'[TimeCreated]) < 17, "Workday", "Off Hours" )Reference link:
HOUR function (DAX) - DAX | Microsoft Learn
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much Joyce! I was super busy this week and didn't get back to this until today. This is very helpful.
