Return text value based on cell time value

[FBarri]

Hi All, 

I have data of agents logging to work  ,the log in time is captured in hour, minute, seconds , I need to add a column that indicates what shift the agent was working at based on log in time 

Name	Log in Time	Shift
James R.	2:34:17 PM	?
John V.	11:53:33 AM	?

Shift  Name	Shift Start Time	Shift End Time
Night	07:00:00 PM	07:00:00 AM
Morning	07:00:00 AM	07:00:00 PM

will it be an if & and formula ? I tried few times but the results were overlapping 

Thank you for you help  🙂

[v-jingzhang · Accepted solution]

Hi @FBarri 

First change the [Log in Time] column to time type. Then add a custom column with below code. 

if [Log in Time] >= #time(7,0,0) and [Log in Time] < #time(19,0,0) then "Morning" else "Night"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Waldo35]

I don't work with AM / PM over here so it automatically converted to 11:53 and 14:34.  Here are a couple suggestions:

1. Change to text value, does it keep the PM and AM, if so change to text value then:

if Text.Contains([Column2], "AM") then "Morning" else "Night")

or still try the:

if [Column2] >= Time.From("12:00") and [Column2] <= Time.From("23:59") then "Night" else "Morning"

Powerbi might understand the time even tho it says AM and PM. But i have no way to test this without changing my PowerBI language settings