Forum Discussion
Return text value based on cell time value
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 🙂
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.
2 Replies
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
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.
