Elapsed time in BO

[budoyboy]

How do I do elapsed time in BO using date and time fields.

 

[ainkca]

What version of Business Objects are you on?

 

[budoyboy]

We have 11.5

 

[skrandel]

You don't do elapsed time directly. BO has no functionality for doing time math. You have to take your date fields convert them to a number of seconds or minutes since a certain date. Then you have to do the math your self.

Better yet, do this in your universe. The databases do this rather well.

Steve Krandel
Intuit

 

[budoyboy]

Thanks I have done that in Crystal.

 

[budoyboy]

Steve you don't happen to some sample code for that???

 

[budoyboy]

I figured it out, if anyone wants a copy let me know.

 

[budoyboy]

This is not my solution, I got it from another forum

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(<BeginDate>,<EndDate>) * 24 * 60 * 60)

2. Create a <Start Seconds> variable for the "BeginDate":
=(ToNumber(FormatDate(<BeginDate > ,"HH"))*60*60) +(ToNumber(SubStr(FormatDate(<BeginDate > ,"HH:mm") ,4 ,2))) *60 +(ToNumber(SubStr(FormatDate(<BeginDate> ,"HH:mm:ss") ,7 ,2)))

3. Create a <End Seconds> variable for the "EndDate", but this time adding the <Days_into_Seconds> to this as well:
=((ToNumber(FormatDate(<EndDate > ,"HH"))*60*60) +(ToNumber(SubStr(FormatDate(<EndDate > ,"HH:mm") ,4 ,2))) *60)+(ToNumber(SubStr(FormatDate(<EndDate> ,"HH:mm:ss") ,7 ,2))) + <days into seconds>

4. Subtract them to get the difference between these two dates (call this variable <Seconds Difference>) in seconds.
=<End Seconds>-<Start Seconds>

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

If you want to display Hours Only, use the following instead of #5:
=FormatNumber((Floor(Truncate((<Seconds Difference> / 3600) ,0))) ,"00")