Forum Discussion

FBarri's avatar
FBarri
New Member
3 years ago
Solved

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...
General Comment
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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's avatar
Waldo35
Helper I
3 years ago

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