Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 27, 2015 at 05:07 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2015 at 05:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 27, 2015 at 06:08 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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