Diff between two dates

[system]

I hate to give good news and bad news in the same reply but… first thank you for your help.

It works great and all looks good. When I created the second variable i get an error message. "Not enough paramerters DMB0006 My curser starts flickering here

=FormatNumber(Floor(

---

djohnson (BOB member since 2007-07-26)

[system]

How about the same without carriage returns (no extra “enters”):

=FormatNumber(Floor(<number of seconds>/86400) ,"0") &amp; " day(s) " &amp; FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") &amp; ":" &amp; FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") &amp; ":" &amp; FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")

Did you give the first variable the name “number of seconds”? If you used any other name then you need to change the above formula accordingly.

---

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

[system]

OMG… You are the greatest! Thank you for all your help… If you havnt figured it out yet… it worked.

---

djohnson (BOB member since 2007-07-26)

[system]

@djohnson

OMG… You are the greatest! Thank you for all your help… If you havnt figured it out yet… it worked.

Perfect 8) I am glad it helped.

---

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

[system]

Hi

This has been fantastic advice and I have managed to recreate it for my use as specified. I have one question that puts a slightly different slant on it in that I would like to display my elapsed time in hours, minutes and seconds. Therefore if it goes over 24 hours, I’d still like my results to show something like 27:16:23. I don’t really understand Mod so any pointers would be really appreciated.

---

jmmorton (BOB member since 2004-07-05)

[system]

Hi Jonno,

Then try the formula from this post:

Convert number of seconds to actual time

We have a value in our database that is the number of seconds past midnight. Is there a way to convert this to hh:mm:ss in Business Objects (hh:mm would suffice) Many thanks in anticipation caz (BOB member since 2007-04-11)

---

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

[system]

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

---

jmmorton (BOB member since 2004-07-05)

[system]

Hi Mark,

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

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…

Thanks in advance,

SAM

---

sam4u (BOB member since 2008-01-09)

[system]

@sam4u

I tried your solution but it didn’t worked

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]

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]

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]

Hi,

@figdatbong

is there an easy way to add a time field to a date field to give a date time field?

This topic may help:

Changing the time portion of timestamp

Hi I just wanted to add an hour with my existing timestamp, your input is more appreciated. I wanted to do this only on the Report and not on the universe. Say the time in the column 14-08-2009 14:30:00, i wanted to display it as 14-08-2009 15:30:00 Regards Siva.M looksmee (BOB member since 2006-02-08)

---

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

[system]

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]

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]

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]

Hi,

What formula is used in the [ResponseTime] variable?

---

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

[system]

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]

Hi,

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

@jonesa75

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

Try without it if it works.

---

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

[system]

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]

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

---

Jansi (BOB member since 2008-05-12)