Evaluate Time

[amulder]

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

[v-frfei-msft · Accepted solution]

Hi @amulder,

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]

Hi @amulder,

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

Regards,
Frank

[Anonymous]

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)

[amulder]

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.