Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Evaluate Time

Hi,

 

I am trying to evaluate a time value in a query with a calculated column.  I have 2 columns; Login Polling Interval and Logout Polling Interval.  If the record is considered logged in at 0600 then I want a 1 in the column.  If the record is considered logged out at 0600 then I want a 0 in the column.  The Login and Logout values are in 24 hour clock and can span days.  So for example the first record logged in at 20:18 and stayed logged in until 06:45 the next morning. So in this record the 0600 column should have a 1 because it was logged in at 0600.

 

I don't care about the spanning of days, I'm just looking for an evaluation of the 0600 time block.  This is what my formula currently is but it is not working:

 

0600 = IF([LOGIN_TIME]=BLANK(),BLANK(),IF([Login Polling Interval]<=TIMEVALUE("06:00")&&[Logout Polling Interval]>=TIMEVALUE("06:00"),1,0))

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    I made one sample for your reference.

     

    0600 =
IF (
    ISBLANK ( Table1[LOGIN_TIME] ),
    BLANK (),
    IF (
        Table1[Login Polling Interval] > Table1[Logout Polling Interval]
            && TIMEVALUE ( "06:00" ) < Table1[Logout Polling Interval],
        1,
        IF (
            [Login Polling Interval] <= TIMEVALUE ( "06:00" )
                && [Logout Polling Interval] >= TIMEVALUE ( "06:00" ),
            1,
            0
        )
    )
)

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, you can easily create conditional column based on Login_hrs, Login_min, Login_AMPM and Logout_hrs. Below is the formula:

     

    0600 =  VAR Login_Hr = VALUE(LEFT(Table1[Login],SEARCH(":",Table1[Login])-1)) RETURN
                 VAR Login_Min = VALUE(MID(Table1[Login],SEARCH(":",Table1[Login])+1,2)) RETURN
                 VAR Login_AMPM = MID(Table1[Login],SEARCH(":",Table1[Login])+1,2) RETURN
                 VAR Logout_Hr = VALUE(LEFT(Table1[Logout],SEARCH(":",Table1[Logout])-1)) RETURN
                 VAR Logout_Min = VALUE(MID(Table1[Logout],SEARCH(":",Table1[Logout])+1,2)) RETURN

     

                 IF(OR(OR(
                                 AND(Login_Hr <6, Login_AMPM = "AM"),
                                 AND(AND(Login_Hr =6, Login_Min = 0),Login_AMPM ="AM")),
                    OR(
                                 Logout_Hr >6,
                                 AND(Logout_Hr =6,Logout_Min >0))),
                    1,0)

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The SEARCH function is failing saying it cannot find ":".  The Login time is drawn from a calculated column in which it is formatted with ":", not manually typed in.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

    I made one sample for your reference.

     

    0600 =
IF (
    ISBLANK ( Table1[LOGIN_TIME] ),
    BLANK (),
    IF (
        Table1[Login Polling Interval] > Table1[Logout Polling Interval]
            && TIMEVALUE ( "06:00" ) < Table1[Logout Polling Interval],
        1,
        IF (
            [Login Polling Interval] <= TIMEVALUE ( "06:00" )
                && [Logout Polling Interval] >= TIMEVALUE ( "06:00" ),
            1,
            0
        )
    )
)

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,
      Frank