Number of hours between two dates

[system]

can any one please help me on the following request.

I have the following objects Sample Date and the Received date. The data is shown below:

Sample Date -----------------------Received Date--------------- Total Hours

01-01-2012 12:45:30 PM ----------01-30-2012 09:35:21 AM---------?
01-05-2012 12:45:30 PM ----------01-10-2012 09:35:21 AM---------?
03-01-2012 09:35:21 AM-----------05-01-2012 09:35:21 PM---------?

I need to calculate the Total Hours,
Total Hours = Received date (Minus) Sample Date.

DaysBetween function worked to calculate the Number of days in between but similarly how to calculate number of hours between two dates, is there any specific function for that or do i need to write the any logic.
Please suggest me what to do?

Please let me know what kind of a logic I need to use to implement this.

Thank you.
Boez

---

Boez (BOB member since 2011-12-08)

[system]

Hi,

@Boez

is there any specific function for that

There is none.

@Boez

or do i need to write the any logic.
Please suggest me what to do?

This old topic can get you started:

Diff between two dates

I am trying to create a variable in Desktop Intelligence XI that will give me the diff between two dates. I used “=DaysBetween(<Admit Date & Time> ,<Order Date & Time>)” and I get just days. Is there a way to get his broke down to hours, minutes and seconds? djohnson (BOB member since 2007-07-26)

You would need to re-write formulas from DeskI syntax to WebI syntax though.

---

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

[system]

Or even

= 
((( 
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 +
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 + 
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 7 ; 2 ) )
 ) 
 - 
 (
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 1 ;2 ) ) * 60 * 60 +
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 +
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 7 ; 2) )
 )
 )
) / 60 +
( DaysBetween( [Oldest timestamp] ; [Most recent timestamp] ) * 24 * 60 )

[Moderator Edit: Added code formatting - Andreas]

---

mikca (BOB member since 2005-12-16)

[system]

Hello Marek,

Thank you for ur reply.

I Exactly did what you said, it worked fine…

First Logic created with object name [Test]

=DaysBetween( [Receiveddt] ; [U Receivedt]) * 86400 + (ToNumber(FormatDate([U Receivedt] ;"HH")) * 3600 + 
 ToNumber(Left(FormatDate([U Receivedt] ;"mm:ss") ;2)) * 60 + ToNumber(FormatDate([U Receivedt] ;"ss")) 
) 
- 
( ToNumber(FormatDate([Receiveddt];"HH")) * 3600 + ToNumber(Left(FormatDate([Receiveddt] ;"mm:ss") ;2)) * 60 + 
 ToNumber(FormatDate([Receiveddt] ;"ss")) 
)

Second Logic

=FormatNumber(Floor([Test]/86400) ;"0") + " day(s) " + 
FormatNumber(Floor(Mod([Test] ;86400)/3600) ;"00") + ":" +
FormatNumber(Floor(Mod(Mod([Test] ;86400) ;3600)/60) ;"00") + ":" +
FormatNumber(Mod(Mod(Mod([Test] ;86400) ;3600) ;60) ;"00")

It worked fine, but I tried the look at the data in different way.

For example:

Recieve Date -------------------------------Received Date---------------------------- Time gap ----------------------Total Hour
09/16/2012 3:27:01 PM -------------11/2/2012 2:47:04 PM------------46 days(s) 23:20:03----------------------1127 hrs

So your logic is looking good, its calculating the Time gap between two time stamps, but I am looking for just hours between them.

I tried changing the logic little bit but it end up showing the result as 23 Hours instead of 1127 hours, I guess my logic did not calculated the days.

Please let me know if there is an easy way to write that kind of logic.

Thank you
Boez

---

Boez (BOB member since 2011-12-08)

[system]

Hi,

Your variable [Test] gives the time difference in seconds. So just divide it by 3600 to get the result in hours. Or am I missing something?

---

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