6 Replies Latest reply: Mar 10, 2017 1:36 AM by Chanin D'Souza RSS

    Time Difference in Hours

    Chanin D'Souza

      Hi to all,

      I want to find the 'Time Difference in hours'. However if the date is of the previous day, it shows the difference of hours in negative

      For example:

      Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#('3/7/2017 11:00:00 PM','M/D/Y h:mm:ss TT'),'hh'))

      Where Now=('8/3/2017 8:00:00 AM')

      The ans is comng negative as -14.

       

      The main Equation I want to use this function is in Time Buckets.

      The real time Bucket equation is given below.

      Please help me rectify the error in the equation.

      (Time Bucket Equation)

       

      if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 1,'< 1 hour',

      if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 2,'< 2 hours',

      if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 3,'< 3 hours',

      if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 4,'< 4 hours','> 4 hours'))))


      Where, AND_allocatedDate2 = Field Name


      Thanks in advance.