BusinessObjects Board

Subtract Dates at the report level

Is it possible to take 2 date time stamps and subtract them at the report level? The Dates are in mm/dd/yyyy hh:mm:ss format. I am looking to get a 0.00 number as the end result.

Foggy (BOB member since 2005-10-10)

Nope. BO doesn’t do time math at all.

You can try the datediff function, but I doubt it will help.

Steve Krandel :us: (BOB member since 2002-06-25)

Has any progress been made on this topic or is Business Objects statisifed with not doing math?

TGSW02 (BOB member since 2008-08-09)

Might want to try the daysbetween function. It doesn’t appear to keep the fraction. If the fraction is important you might want to create a set of variables as follows (note: you can do this all in 1 variable if you are comfortable in doing so):

Remember, there are 86,400 seconds in a day so…

seconds till midnight for begin date
=86400 - tonumber(formatdate([from date];“HH”)) * 3600 + tonumber(formatdate([from date];“mm”)) * 60 + tonumber(formatdate([from date];“ss”))

likewise, seconds from midnight on to date…
=tonumber(formatdate([to date];“HH”)) * 3600 + tonumber(formatdate([to date];“mm”)) * 60 + tonumber(formatdate([to date];“ss”))

finally, take your daysbetween (in seconds) and add your till midnight and your from midnight values

=daysbetween([from date];[to date]) * 86400 + [seconds till midnight] + [seconds from midnight]

I believe that will give you your answer in seconds, finally, divide the entire formula by 24 for the # of days with the fractional portion.

cablegp (BOB member since 2003-06-04)

Something similar, but for Deski, has been recently discussed here:

The final formulas can be easily translated from DeskI to WebI syntax.

Marek Chladny :slovakia: (BOB member since 2003-11-27)