SAP webi formula to calculate the hours and minutes between two dates

[Raz11]

Hi Experts,

Is there a more accurate formula for calculating the difference between two dates in hours and minutes in SAP web intelligence?

I need to determine the time difference between two dates. For instance, given the following dates:

- date1: 07/06/2024 09:52:17 PM
- date2: 07/07/2024 02:25:39 AM

The correct difference is 4:33, but the formula I used yielded a result of 5:01. Can you suggest a better formula to get the correct result?

=((((ToNumber(Substr(FormatDate([Performed Date & Time] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Order Date & Time] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Performed Date & Time]; "HH:mm:ss") ; 7 ;2 ) )) - (ToNumber(Substr(FormatDate([Order Date & Time] ; "HH:mm:ss") ; 1 ;2 ) ) * 60 *60+ToNumber(Substr(FormatDate([Order Date & Time] ; "HH:mm:ss") ; 4 ;2) ) * 60 +ToNumber(Substr(FormatDate([Order Date & Time] ; "HH:mm:ss") ; 7 ;2 ) ) ))) / 60 + (DaysBetween([Order Date & Time]; [Performed Date & Time]) * 24 * 60) ) / 60

Regards.

[ayman_salem · Accepted solution]

Use the TimeBetween() function with the "SecondPeriod" and then convert it to the value you want (minute, hours, or days).

The example here is in hours and the correct answer is 4.56 hours.

v_Diff_Between:
=TimeBetween([Performed Date & Time];[Order Date & Time];SecondPeriod)/(60*60)

...

Hope it helps

 

[Raz11 · Accepted solution]

I am appreciating for your efforts.

I used your formula in this link and obtained the correct result 4.33.22.

Solved: Convert Solid number to 00:00:00 format - SAP Community

[ayman_salem · Accepted solution]

happy to help
both solutions lead to the same result.       The format you need is different: 4.56 (h) = 4:33 (h:mm)

[ayman_salem · Accepted solution]

happy to help
both solutions lead to the same result.       The format you need is different: 4.56 (h) = 4:33 (h:mm)
..

Please accept the answer and close the question

[Savio_D · Accepted solution]

TimeBetween() function should work . https://help.sap.com/docs/SAP_BUSINESSOBJECTS_BUSINESS_INTELLIGENCE_PLATFORM/987ff4008e2d47fb9f92f7b...

[Raz11 · Accepted solution]

What the right formula for this format 4:33 (h:mm) ?

[ayman_salem · Accepted solution]

what I meant that the result is same, one was in the decimal (h) and the other is in (hh:mm)
There is no defined format for converting decimal numbers to hours:minutes. You use the formula I described in the link you follow and you will get the format you want.
Solved: Convert Solid number to 00:00:00 format - SAP Community

and here again the defined variables

TimeBetween:     =TimeBetween([T1];[T2];SecondPeriod)

HH:     =([TimeBetween]-Mod([TimeBetween];3600))/3600

mm:    =(Mod([TimeBetween];3600) - Mod([TimeBetween];60))/60

ss:      =Mod([TimeBetween];3600) - [mm]*60

TimeInFormat:      =FormatNumber([HH]; "00")+ ":" + FormatNumber([mm]; "00") + ":" +FormatNumber([ss]; "00")

[Raz11 · Accepted solution]

Thank you Mr. Ayman for your efforts