Diff between two dates

system · November 17, 2008, 5:17pm

Hi Jonno,

Then try the formula from this post:

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

system · November 20, 2008, 11:15am

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

jmmorton (BOB member since 2004-07-05)

system · May 1, 2009, 3:02pm

Hi Mark,

I have similar problem. I tried your solution but it didn’t worked please help me.

I have 2 dates

Submit date (dd/mm/yyyy hh:mm:ss)
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…

Thanks in advance,

SAM

sam4u (BOB member since 2008-01-09)

system · May 1, 2009, 11:40pm

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)

system · May 1, 2009, 11:48pm

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)

system · October 22, 2009, 2:49pm

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)

system · October 22, 2009, 3:01pm

Hi,

This topic may help:

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

system · October 26, 2009, 11:45am

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)

system · December 1, 2009, 8:28pm

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)

system · February 13, 2010, 1:06pm

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)

system · February 13, 2010, 5:20pm

Hi,

What formula is used in the [ResponseTime] variable?

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

system · February 14, 2010, 3:41am

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)

system · February 16, 2010, 11:00pm

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)

system · February 17, 2010, 4:15am

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)

system · February 17, 2010, 5:01am

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

Jansi (BOB member since 2008-05-12)

system · February 17, 2010, 1:48pm

Here is the code I tried:

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

Thanks,

Andy

jonesa75 (BOB member since 2010-02-11)

system · November 16, 2010, 2:42pm

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)

system · January 25, 2013, 1:26pm

Really good one thanks a lot

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

system · March 24, 2017, 9:47am

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)

usmantaruq1158 · February 26, 2021, 1:44am

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