Anyone know how to calculate the difference in hours between to objects that
have date and time?
Thanks,
Shirley Knight shkk@ti.com BO support at Texas Instruments
Listserv Archives (BOB member since 2002-06-25)
At 04:54 PM 1/6/99 -0600, you wrote:
Anyone know how to calculate the difference in hours between to objects that
have date and time?Thanks,
Shirley Knight shkk@ti.com BO support at Texas Instruments
*** A HAPPY NEW YEAR TO ALL ***
Hi Shirley:
While not the most elegant solution, you could use the DaysBetween
Function to get the number of Days and multiply that with 24 for number
hours. The rest of the hours can be calculated by extracting the hours
portion of the two dates in a 24 hour clock format and subtracting the two.
Hope it helps. If you need a detailed example, please email me and I will
send you one.
Regards,
Chander Aggarwal
COMPUEXCEL http://www.compuexcel.com
The home of BusinessObjects Essentials - the Worlds First and Leading Full
Multimedia Computer Based Training For BusinessObjects, with over 10,000
users world-wide!
Listserv Archives (BOB member since 2002-06-25)
The following is a solution posted a few months ago by Dave Rathburn:
I am including a formula that will do the work for you on the report side. I
have used this before… caveats listed after the formula.
Assume DATE1 and DATE2 are date objects that you need to subtract.
Warning: ugly code ahead!
=((( (ToNumber(SubStr(FormatDate( ,“HH:MM:SS”) ,1 ,2))6060) +
(ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 4, 2))* 60) +
ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 7, 2))) - (
(ToNumber(SubStr(FormatDate( ,“HH:MM:SS”) ,1 ,2))6060) +
(ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 4, 2))* 60) +
ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 7, 2)))) / 3600) +
(DaysBetween( ,)*24)
Caveat 1: This formula assumes that no date value will ever be null.
Caveat 2: There is a known performance degradation when using the
FormatDate() or FormatNumber() function in a BusinessObjects report.
Caveat 3: No provision is made for ignoring weekends, but then again, you
would have problems doing that with database functions as well. Some people
would like to see the difference between noon on Friday and noon on the
following Monday as 1 day (24 hours). Hopefully this is not an issue for
you!
Pseudo code for formula:
-
Translate time portion of date to a character string with
FormatDate()
2. Use SubStr() to strip out the time information as Hours, Minutes,
and
Seconds
3. Use ToNumber() to translate each component back to a number
4. Multiply by an appropriate factor to translate each time component
into seconds
5. Subtract the two numbers
6. Translate result back into hours by dividing by an appropriate
factor
7. Add the number of hours per day obtained from DaysBetween() * 24
The net result is the difference between two dates as a decimal number of
hours. To obtain days, simply divide the formula result I have provided by
24. Some sample results are shown below, where DATE1 and DATE2 are date
objects and the last column is the result of the included formula. Rather
than take up bandwidth to do a full explanation of the formula, I will leave
that as an exercise for the reader. If you have any problems, feel
free to drop me an email directly or post a follow-up to the list.
Note: if you copy and paste the info below into notepad then the spacing
should be correct. Proportional fonts in email make formatting a challenge
sometimes…
DATE1 DATE2 Time Diff
06/16/98 1:36:39 AM 06/17/98 3:31:51 PM 37.92
06/16/98 2:14:05 PM 06/17/98 2:14:05 AM 12.00
06/16/98 2:14:33 PM 06/16/98 8:14:33 PM 6.00
06/17/98 2:07:58 AM 06/17/98 1:39:10 PM 11.52
06/17/98 6:24:50 AM 06/18/98 1:22:26 PM 30.96
06/17/98 12:38:47 PM 06/17/98 1:50:47 PM 1.20
06/17/98 1:51:13 PM 06/18/98 1:51:13 AM 12.00
-----Original Message-----
Sent: Thursday, January 07, 1999 04:04 PM
At 04:54 PM 1/6/99 -0600, you wrote:
>Anyone know how to calculate the difference in hours
between to objects that
>have date and time?
>
>Thanks,
>Shirley Knight shkk@ti.com BO support at Texas
Instruments
>
*** A HAPPY NEW YEAR TO ALL ***
Hi Shirley:
While not the most elegant solution, you could use
the DaysBetween
Function to get the number of Days and multiply that with 24
for number
hours. The rest of the hours can be calculated by extracting
the hours
portion of the two dates in a 24 hour clock format and
subtracting the two.
Hope it helps. If you need a detailed example, please email
me and I will
send you one.
Regards,
Chander Aggarwal
COMPUEXCEL http://www.compuexcel.com
The home of BusinessObjects Essentials - the Worlds First
and Leading Full
Multimedia Computer Based Training For BusinessObjects, with
over 10,000
users world-wide!
Listserv Archives (BOB member since 2002-06-25)
Shirley,
This may help. It converts diff in hours. To do it in minutes multiply
by 60. The following calculates the difference between Receipt date
and Date last Status created:
=DaysBetween(<Receipt Date>,<Date Last Status
Created>)*24+(((ToNumber(FormatDate(<Date Last Status
Created>,"HH"))+ToNumber(SubStr(FormatDate(<Date Last Status
Created>,"HHmm"),3,2))/60)-(ToNumber(FormatDate(<Receipt Date>,"HH"))+
ToNumber(SubStr(FormatDate(<Receipt
Date>,"HHmm"),3,2))/60)))
This may slow down the report a bit. May be function on the database server
side will be faster.
Manoj
______________________________ Reply Separator _________________________________
Author: Business Objects Query Tool BUSOB-L@LISTSERV.AOL.COM at INTERNET
Date: 01/06/1999 4:54 PM
Anyone know how to calculate the difference in hours between to objects that
have date and time?
Thanks,
Shirley Knight shkk@ti.com BO support at Texas Instruments
Listserv Archives (BOB member since 2002-06-25)
Chander;
Please provide specifics/samples.
Thanks
Bruce Hinrichs
Schneider National, Inc.
Green Bay, WI
HinrichsB@Schneider.Com
(920) 592-2098
(920) 592-2188 - FAX
Date: Thursday, January 07, 1999 5:13PM
At 04:54 PM 1/6/99 -0600, you wrote:
Anyone know how to calculate the difference in hours between two objects
that
have date and time?Thanks,
Shirley Knight shkk@ti.com BO support at Texas Instruments
*** A HAPPY NEW YEAR TO ALL ***
Hi Shirley:
While not the most elegant solution, you could use the DaysBetween
Function to get the number of Days and multiply that with 24 for number
hours. The rest of the hours can be calculated by extracting the hours
portion of the two dates in a 24 hour clock format and subtracting the two.
Hope it helps. If you need a detailed example, please email me and I will
send you one.
Regards,
Chander Aggarwal
COMPUEXCEL http://www.compuexcel.com
The home of BusinessObjects Essentials - the Worlds First and Leading Full
Multimedia Computer Based Training For BusinessObjects, with over 10,000
users world-wide!
Pls report bounces in response to postings to
BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in
BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
Listserv Archives (BOB member since 2002-06-25)
At 03:03 PM 1/8/99 -0600, you wrote:
Chander;
Please provide specifics/samples.
Thanks
Bruce Hinrichs
From: Chander Aggarwal
Date: Thursday, January 07, 1999 5:13PM
At 04:54 PM 1/6/99 -0600, you wrote:
Anyone know how to calculate the difference in hours between two objects
that
have date and time?Thanks,
Shirley Knight shkk@ti.com BO support at Texas Instruments
Hi Bruce:
Here is an example I put together. I must say that I have not spent enough
time to fully debug/test it but it should give you some idea.
Assumptions:
1. Date1 is always Less Than Date2
2. I am not concerning myself with the minutes and seconds
-- just with the hours. The reader can easily expand this
example to include the minutes and seconds.
Solution:
Date1 Hours = (24-ToNumber(FormatDate(<Date1>,"HH")))
Date2 Hours = ToNumber(FormatDate(<Date2>,"HH"))
HoursBetween = ((DaysBetween(<Date1>,<Date2>)-1)*24) +
<Date1 Hours> + <Date2 Hours>
Again, please test it thoroughly to ensure that it works correctly! I have
to subtract 1 from the result of the DaysBetween function because it does
not care about the number of hours.
Consider the following example:
Date1 = Jan 10/99 19:00:00
Date2 = Jan 12/99 05:00:00
The result of DaysBetween function in BO will be 2 – this is not true if
you are trying to calculate the number of hours. This is even more obvious
if Date1 and Date2 are only a few hours apart!
Hope this helps.
Regards,
Chander Aggarwal
COMPUEXCEL http://www.compuexcel.com
The home of BusinessObjects Essentials - the Worlds First and Leading Full
Multimedia Computer Based Training For BusinessObjects, with over 10,000
users world-wide!
Listserv Archives (BOB member since 2002-06-25)