0

# Webi: How to calculate time differencein Hrs between 2 dates

Nov 15, 2016 at 06:52 PM

205

Former Member

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

Former Member Nov 15, 2016 at 07:16 PM
0

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

Share
AMIT KUMAR
Nov 15, 2016 at 08:24 PM
0

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

Show 1 Share
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

Former Member Nov 16, 2016 at 09:28 AM
0

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,

Share
Former Member Nov 16, 2016 at 11:09 PM
0

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

Share
Former Member Nov 17, 2016 at 04:04 PM
0

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.

Share