Forum Discussion

gkakun's avatar
gkakun
Helper III
5 years ago
Solved

Time Comparison

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?

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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")

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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")

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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