BusinessObjects Board

Diff between two dates

building reports desktopintelligence
#21

Hi,

I used the formula to calculate the date time difference as you posted and converted back to hours by dividing by 3600. I need to go a step further to count the time difference if it is 4 hours or less, but keep receiving an error. I created a simple variable to troubleshoot

=If(ToNumber([ResponseTime])<=4;1;0)

It returns an #Error. Any suggestions?

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

#22

Hi,

What formula is used in the [ResponseTime] variable?

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

#23

Sorry.

=(DaysBetween([WO Call Date Time];Min([IVR Time])) * 86400 + (ToNumber(FormatDate([IVR Time];"HH")) * 3600 + ToNumber(Left(FormatDate([IVR Time];"mm:ss");2)) * 60 + ToNumber(FormatDate([IVR Time];"ss"))) - (ToNumber(FormatDate([WO Call Date Time];"HH")) * 3600 + ToNumber(Left(FormatDate([WO Call Date Time];"mm:ss");2)) * 60 + ToNumber(FormatDate([WO Call Date Time];"ss"))))/3600

WO Call Date Time and IVR Time are both in Date Time format.

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

#24

Hi,

Your [ResponseTime] variable is already in the number format. So why do you need to convert it to number using ToNumber() function?

Try without it if it works.

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

#25

Marek,

It gives the following error

“The expression/sub-expression at position 4 uses an invalid data type. Specify a valid data type. (Error: WIS 10037)”.

Any suggestions?

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

#26

Please post the exact code you used after Marek’s suggestion.

Jansi :india: (BOB member since 2008-05-12)

#27

Here is the code I tried:

=If([Response Time]<=4;1;0)

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

#28

I was able to use your info to build a variable that shows the date difference in hours. However now the customer is asking for only business hours. Is there a way to build in for it to only calculate business hours? Example: They want to see how long a helpdesk ticket has been open but only for the business hours (8 to 5) that is has been open (so don’t count the time outside of business hours.

Below is the variable I am currently using to get the difference in hours:

=(((ToNumber(Substr(FormatDate([Current Date] ;“HH:mm:ss”) ; 1 ; 2 ) ) * 60 * 60 ) + (ToNumber(Substr(FormatDate([Current Date] ;“HH:mm:ss”) ; 4 ; 2 ) ) * 60) + (ToNumber(Substr(FormatDate([Current Date] ;“HH:mm:ss”) ; 7 ; 2)))) - ((ToNumber(Substr(FormatDate([Open].[Change Execution Start Time] ;“HH:mm:ss”) ;1 ;2)) * 60 * 60 ) + (ToNumber(Substr(FormatDate([Open].[Change Execution Start Time] ;“HH:mm:ss”) ; 4 ; 2)) * 60) + (ToNumber(Substr(FormatDate([Open].[Change Execution Start Time] ;“HH:mm:ss”) ; 7 ; 2))))) / 3600 + (DaysBetween([Open].[Change Execution Start Time];[Current Date]) *24)

kelly3927 (BOB member since 2010-10-27)

#29

Really good one thanks a lot

pavan.au143 (BOB member since 2011-12-29)

#30

Thanks for all this help, I have successfully found the difference in hours between two dates! [TimeDiff]
I now want to record the total times within a month, =Sum[TimeDiff] works expect where the start date and end date are in different months.
How do I get it to associate the [TimeDiff] with the end date month?
Or some other way of doing it?

chramm (BOB member since 2017-03-23)

#31

for me the 2nd variable is not picking up the &amp function

unrecognized input ‘&’ at 48 position 48.

also i believe instead of using , i had to use ; in my variable.

i am currently using webi 4.2