Forum Discussion

AI14's avatar
AI14
Helper III
3 years ago

if statement time based

Hi

 

Is there a formula to compare 2 times if less than or more than a fixed time for example,

 

Actual 09:59

Target 10:00

 

if Actual is less than Target by 10 mins then 1 if not 0

 

 

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi AI14 

    IF ( DATEDIFF ( 'Table[Actual], 'Table[Target], MINUTE ) = 10, 1, 0 )

    • AI14's avatar
      AI14
      Helper III
      3 years ago

      Thanks,

       

      Would i need to add a < to say if its less than?

  • BrianConnelly's avatar
    BrianConnelly
    Resolver III
    3 years ago

    For this we will convert your time to DateTime using the TimeValue and then calculate minutes using DateDiff.

    DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE)

     

    Then build your switch statement or if statement for the logic.  Many ways to do this, but here are a few...

    SWITCH(TRUE()
,DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE) > 10, 1
,0)

     

    IF(DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE)>10,1,0)