Time Comparison

[gkakun]

Hi, I have a table with timestamp and Im trying to build the logic, if the time is between 20:15 and 8:14 it's "night shift", else, it's" day shift". I tool the hour to different column but the logic doesnt work. Test = if(and('Cust Order Cube'[Create Time]>=TIME(20,15,0),'Cust Order Cube'[Create Time]<=TIME(8,14,59)),"Night","Day") any idea?

[v-easonf-msft]

Hi,  @gkakun 

Here I use  field [Create Date]  rather than [Create Time] to create a calculated column as below:

Test =
IF (
    AND (
        FORMAT ( 'Cust Order Cube'[Create Date], "hh-mm-ss" )
            >= FORMAT ( TIME ( 20, 15, 0 ), "hh-mm-ss" ),
        FORMAT ( 'Cust Order Cube'[Create Date], "hh-mm-ss" )
            <= FORMAT ( TIME ( 8, 14, 59 ), "hh-mm-ss" )
    ),
    "Night",
    "Day"
)

 Best Regards,
Community Support Team _ Eason

[amitchandak · Accepted solution]

@gkakun , Logic seem right. Try Or and  Try with .time

Test = if(OR('Cust Order Cube'[Create Time]>=TIME(20,15,0),'Cust Order Cube'[Create Time]<=TIME(8,14,59)),"Night","Day")

Test = if('Cust Order Cube'[Create Time].time>=TIME(20,15,0) || 'Cust Order Cube'[Create Time].time<=TIME(8,14,59),"Night","Day")