BusinessObjects Board

elapsed time

building reports desktopintelligence
#1

I have Admit date and time in 2 separate fields and the same for the Discharge date and time

How do I figure out the hours that have elapsed using the 4 fields
Thanks

Using DI XI

alexmyboy (BOB member since 2009-02-03)

How to Calculate Time Difference
#2

Ok, try this. I am going to assume that you have access to Universe designer and that the 4 columns of data are datatype “date”. In designer, create “Admit Date” by concatenating admit date and admit time. Do the same for “Discharge Date”. Also, the database that you are using may have an impact on what functions you need to use to concatenate these two columns to achieve the proper format.

As to the formula for achieving elapsed time:

In Deski, follow the steps below: This formula actually converts to seconds. Better to have it all and only show what you need:

Now, use your two new dates as replacements for “BeginDate” and an “EndDate” . Both contain a time stamp and you want to calculate the difference in time (hh:mm:ss) between these two dates.

Follow these steps to calculate the time between two dates in “hh:mm:ss” format:

  1. Create a variable called <Days_into_Seconds>. This will calculate the days between and converts then into seconds:
    =(DaysBetween(,) * 24 * 60 * 60)

  2. Create a variable for the “BeginDate”:
    =(ToNumber(FormatDate( ,“HH”))6060) +(ToNumber(SubStr(FormatDate( ,“HH:mm”) ,4 ,2))) *60 +(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7 ,2)))

  3. Create a variable for the “EndDate”, but this time adding the <Days_into_Seconds> to this as well:
    =((ToNumber(FormatDate( ,“HH”))6060) +(ToNumber(SubStr(FormatDate( ,“HH:mm”) ,4 ,2))) *60)+(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7 ,2))) +

  4. Subtract them to get the difference between these two dates (call this variable ) in seconds.
    =-

  5. To convert this to “hh:mm:ss” format, use the following formula:
    =FormatNumber((Floor(Truncate(( / 3600) ,0))) ,“00”)+":"+FormatNumber((Floor(Truncate(Mod( , 3600) , 0) / 60) ) ,“00”)+":"+FormatNumber(Floor(Mod(Mod( , 3600) , 60) ) ,“00”)

If you want to display Hours Only, use the following instead of #5:
=FormatNumber((Floor(Truncate(( / 3600) ,0))) ,“00”)

Hope this helps

MBTDC :us: (BOB member since 2007-12-28)

#3

Thanks I never got notified you replied in ithe BO Desktop Forum

I will try what you sent me but I found another way to do, it’s not very efficient so if yours works that would be great.

Funny thing is I called our Corporate Help Desk and they said it could not be done and I proved them wrong. Good feeling

alexmyboy (BOB member since 2009-02-03)

#4

There is one slight problem in using the function “DaysBetween” in Deski.

Let me give an example:

Start Date 7/19/2009 23:45:00

End Date 7/20/2009 1:00:00

There is only a difference of 15 minutes in these two dates right? But if you use the function DaysBetween(Start Date, End Date) the out put is 1. Hence if you do DaysBetween(Start Date, End Date) * 24 *60 *60, your results are obviously exaggerated since the function takes into account the difference in “date” days and not the actual time elapsed.

Long time ago some where in this forum I believe, i saw similar calculation by using relative date by fixing a value some 50yrs back like 1:00:00 AM @ 1/1/1950 and calculating the the exact time elapsed by subtracting the number of seconds between two dates since then. After much searching still I cannot find that post. Can anyone please help me in this regard?

Your help would be much appreciated.

Thanks in Advance

edyl (BOB member since 2005-10-03)