BusinessObjects Board

how to calculate two dates with HH:mm:ss output?

building reports webintelligence
#1

I am trying to calculate the difference of 2 date fields, [FRST_TOUCH_TS], and [CRT_TS] . I need the result to look like HH:mm:ss.

I tried different ways like:
=DaysBetween([FRST_TOUCH_TS] ;[CRT_TS]) * 86400
+
(
ToNumber(FormatDate([CRT_TS] ;“HH”)) * 3600 +
ToNumber(Left(FormatDate([CRT_TS] ;“mm:ss”) ;2)) * 60 +
ToNumber(FormatDate([CRT_TS] ;“ss”))
)

(
ToNumber(FormatDate([FRST_TOUCH_TS] ;“HH”)) * 3600 +
ToNumber(Left(FormatDate([FRST_TOUCH_TS];“mm:ss”) ;2)) * 60 +
ToNumber(FormatDate([FRST_TOUCH_TS] ;“ss”)))

But the formula above only gives me a number (e.g. 0.83)… How do I make the result show in a HH:mm:ss format?

Anyone can help?

Thank you very much in advance.

dlam20100322 (BOB member since 2013-05-13)

#2

Welcome to B:bob:B!

Your formula’s result should be the number of seconds between the two dates.

To convert that number of seconds to hh:mm:ss format, check this old topic where a formula for DeskI is used:

It should be easy to convert the formula to the WebI syntax.

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

#3

got it… Thanks for the suggestion.

dlam20100322 (BOB member since 2013-05-13)