Simple Workday/OffHours custom column

[SarahHope]

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

[Anonymous · Accepted solution]

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.

[SarahHope]

Thank you so much Joyce!  I was super busy this week and didn't get back to this until today.  This is very helpful.  

[lbendlin]

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

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/1447...