Skip to Content
avatar image
Former Member

Webi: How to calculate time differencein Hrs between 2 dates

Hi Experts,

Is there a more effective formula other than th eone below to calculate difference in dates(hrs) and (min)

=((((ToNumber(Substr(FormatDate([Entry Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Entry Timestamp]; "HH:mm:ss") ; 7 ;2 ) )) - (ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 7 ;2 ) ) ))) / 60 + (DaysBetween([Exit Timestamp] ; [Entry Timestamp]) * 24 * 60) ) / 60

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Nov 15, 2016 at 07:16 PM

    Also with this formula , used to calculate (mins) - if value is more than 1000 it is showing as #ERROR in the column cell

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 15, 2016 at 08:24 PM

    Try this.You will get the difference between seconds.

    =ToNumber(FormatDate("SECOND DATE" ;"HH")) * 3600 +
    ToNumber(Left(FormatDate("SECOND DATE" ;"mm:ss") ;2)) * 60 +
    ToNumber(FormatDate("SECOND DATE" ;"ss"))
    )
    -
    (
    ToNumber(FormatDate("FIRST DATE" ;"HH")) * 3600 +
    ToNumber(Left(FormatDate("FIRST DATE";"mm:ss") ;2)) * 60 +
    ToNumber(FormatDate("FIRST DATE" ;"ss"))

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Amit,

      When I use the below formula to calculate (mins) , it is throwing me #ERROR in the cells where mins exceeds 1000. What should I do to eliminate this error. Kindly suggest

      =((((ToNumber(Substr(FormatDate([Entry Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Entry Timestamp]; "HH:mm:ss") ; 7 ;2 ) )) - (ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 7 ;2 ) ) ))) / 60 + (DaysBetween([Exit Timestamp] ; [Entry Timestamp]) * 24 * 60) ) / 60

  • avatar image
    Former Member
    Nov 16, 2016 at 09:28 AM

    Hello Arun,

    You can user DaysBetween Function at Report Level to Calculate #of days between two Dates.

    For Hrs and Seconds you need to Multiply this by 24 and 24*3600 respectively.

    Correct me if i am not clear with your Question :)

    Regards,

    Sushil Padhye

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 16, 2016 at 11:09 PM

    Hi Amit,

    When I use the below formula to calculate (mins) , it is throwing me #ERROR in the cells where mins exceeds 1000. What should I do to eliminate this error. Kindly suggest

    =((((ToNumber(Substr(FormatDate([Entry Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Entry Timestamp]; "HH:mm:ss") ; 7 ;2 ) )) - (ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Exit Timestamp] ; "HH:mm:ss") ; 7 ;2 ) ) ))) / 60 + (DaysBetween([Exit Timestamp] ; [Entry Timestamp]) * 24 * 60) ) / 60

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 17, 2016 at 04:04 PM

    The #ERROR for values greater than 1000 is due to number format. The ToNumber() was trying to convert 1,000 to a number which resulted in an error due to ",". So I simply replaced "," with "" and it worked.

    Add comment
    10|10000 characters needed characters exceeded