I have two dates (start / end) in the format dd: mm: AA HH: mm: ss. in SAP BO WEBI

[Former Member]

I Have two dates (star, end) in the format  dd/MM/YY HH:mm:ss in sap BO WEBI.

How do the difference between them.   I used  the Daysbetween, has another way ?

thanks

Francisco gomes

[Former Member]

Thanks for help Mahboob.

but the  userreponse only return the value that user fill at prompt.

Could i calculate the difference of date ?

Exemple of my problem

start date   01/01/2015   14:30:00

end data 1  01/01/2015  15:30:00

end date 2   02/01/2015  17:30;00

daysbetween  (start date; end date1)   =  1 hour, if  i calculate in hours  , if i calculate in days the value is 0.

daysbetween  (start date; end date2) = 1 day and  4 hours     , but i need the result in hour 

24 + 4  = 28 hours.

[mhmohammed]

Try this, this will give the difference in hrs mins secs, replace the v_Duration in Seconds with the variable you have.

=FormatNumber(Floor([v_Duration in Seconds]/3600);"00") + " hr(s) " + FormatNumber(Floor(Mod(Mod([v_Duration in Seconds];86400);3600)/60);"00") + " min(s) " + FormatNumber(Mod(Mod(Mod([v_Duration in Seconds];86400);3600);60);"00") +" sec(s)"

Let me know if you got it.

Thanks,

Mahboob Mohammed

[mhmohammed]

Hi Francisco,

You'll have to extract the Start Date and End Date by creating variables

v_StartDate = Userresponse("<Enter Start Date (or whatever prompt text you see)>")

v_EndDate = Userresponse("<Enter End Date (or whatever prompt text you see)>")

And then, use the 1st formula that Chandresh provided by replacing Admit Date and Order Date with what we just created, v_StartDate and v_EndDate to create an object that calculates the difference in seconds. And use the 2nd formula by Chandresh to convert those seconds to days hh:mm:ss.

You'll have to update those 2 formulas by replacing commas with semi-colons, "&" sign with a "+" sign, and obviously the <number of seconds> with the object you created in previous step.

Give it a shot and let us know what happens.

Thanks,

Mahboob Mohammed

[Former Member]

DaysBetween() function gives the result only in days, I have done this by using below 2 variables, try this - it worked for me, it could works for you as well...

1.

Create a report-level variable <number of seconds> that calculates the number of seconds between 2 dates:

=DaysBetween(<ADMIT_DATE> ,<ORDER_DATE>) * 86400

+

(

   ToNumber(FormatDate(<ORDER_DATE> ,"HH")) * 3600 +

   ToNumber(Left(FormatDate(<ORDER_DATE> ,"mm:ss") ,2)) * 60 +

   ToNumber(FormatDate(<ORDER_DATE> ,"ss"))

)

-

(

   ToNumber(FormatDate(<ADMIT_DATE> ,"HH")) * 3600 +

   ToNumber(Left(FormatDate(<ADMIT_DATE> ,"mm:ss") ,2)) * 60 +

   ToNumber(FormatDate(<ADMIT_DATE> ,"ss"))

)

2. Then use this formula to format the variable:(no extra "enters"):

Code:

=FormatNumber(Floor(<number of seconds>/86400) ,"0") & " day(s) " & FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") & ":" & FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") & ":" & FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")