# Diff between two dates

Hi Jonno,

Then try the formula from this post:

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

That’s fantastic - worked a treat. Thanks ever so much Marek.

jmmorton (BOB member since 2004-07-05)

Hi Mark,

I have 2 dates

1. Submit date (dd/mm/yyyy hh:mm:ss)
2. Responded date (dd/mm/yyyy hh:mm:ss)

User wants to know the response time in HH:MM:SS

the date in the data base is entered in UTC and we are using a custom function in oracle to extract date from that UTC in to BO based on time zone.

Help me with this as this is really imp…

SAM

sam4u (BOB member since 2008-01-09)

Hi,
when you said that you should have also said what the problem was, how it behaved. Did you get any error message or wrong results or…?

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

Hi Mark,

There was some syntax error ( missing paranthesis) i fixed it an finally it worked. Thanks for your response.

sam4u (BOB member since 2008-01-09)

This is a very interesting article, many thanks for posting it however, is there an easy way to add a time field to a date field to give a date time field?

Regards

Paul

figdatbong (BOB member since 2008-03-27)

Hi,

This topic may help:

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

Hi Marek

I would like to thank you for submitting this and other related posts. Using them as a guide, I have been able to develop a solution that works i.e. determine whether contractors are meeting specified targets.

Many thanks

Regards

Paul

figdatbong (BOB member since 2008-03-27)

Hi Marek,

The solution with the formulae to the time difference worked out well.

You are brilliant. Thank you.

bobuser9 (BOB member since 2007-04-20)

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)

Hi,

What formula is used in the [ResponseTime] variable?

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

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)

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 (BOB member since 2003-11-27)

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)

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

Jansi (BOB member since 2008-05-12)

Here is the code I tried:

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

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

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)

Really good one thanks a lot

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

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)

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