Forum Discussion

morther's avatar
morther
Helper I
9 years ago
Solved

datediff formula with multiple criteria not pulling proper data

I am trying to create a column that pulls downtime minutes between 2 dates excluding hours or non-ooperation 9PM to 5AM.  If the downtime minutes fall between the exclude hours they should probably be set to 0.  This is what I have so far on the formula but much of the results are incorrect.

 

WorkingDowntimeMinutes =
IF (
    (
        EventLog[startdatetime] >= HOUR ( 5 )
            || EventLog[StartTime] <= HOUR ( 21 )
    )
        && (
            EventLog[stopdatetime] >= HOUR ( 5 )
                || EventLog[stopdatetime] <= HOUR ( 21 )
        ),
    DATEDIFF ( EventLog[startdatetime], EventLog[stopdatetime], MINUTE )
)

Any ideas where I am going wrong and how to add an else for the 0?

 

Thanks,

Mike

 

 

  • I got this from Experts-Exchange and it worked.

    IF (  HOUR(EventLog[startdatetime]) >=  5 , EventLog[DowntimeMinutes], 0 )

2 Replies

  • Okay, I am really not understanding this.  I made this a bit simpler but still no proper results.

    test =
    IF ( EventLog[startdatetime] >= HOUR ( 5 ), EventLog[DowntimeMinutes], 0 )

    If I set ">=" then I get DownTimeMinutes but no "0" results.  If i change to "<="  then I get all "0" but no DownTimeMinutes

     

    I would expect the hilighted row to show "0" because the time is before 5 AM.

     

    • morther's avatar
      morther
      Helper I

      I got this from Experts-Exchange and it worked.

      IF (  HOUR(EventLog[startdatetime]) >=  5 , EventLog[DowntimeMinutes], 0 )