6 Replies Latest reply: Mar 10, 2017 1:36 AM by Chanin D'Souza

# Time Difference in Hours

Hi to all,

I want to find the 'Time Difference in hours'. However if the date is of the previous day, it shows the difference of hours in negative

For example:

Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#('3/7/2017 11:00:00 PM','M/D/Y h:mm:ss TT'),'hh'))

Where Now=('8/3/2017 8:00:00 AM')

The ans is comng negative as -14.

The main Equation I want to use this function is in Time Buckets.

The real time Bucket equation is given below.

(Time Bucket Equation)

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 1,'< 1 hour',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 2,'< 2 hours',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 3,'< 3 hours',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 4,'< 4 hours','> 4 hours'))))

Where, AND_allocatedDate2 = Field Name

• ###### Re: Time Difference in Hours

hi,

=Interval(Date(Date#(now(),'DD-MM-YYYY hh:mm:ss'),'HH')-Date(Date#('08-03-2017 11:30:00','DD-MM-YYYY hh:mm:ss'),'HH'))

• ###### Re: Time Difference in Hours

Hi Hema,

I think that expression won't work.

Try this,

=Interval(Date((now()),'HH')-Date(Date#('08-03-2017 11:00:00','DD-MM-YYYY hh:mm:ss'),'HH'))

• ###### Re: Time Difference in Hours

hi silambarasan,

I think your date format check and  Change the date format try it...

=Interval(Date(Date#(now(),'DD-MM-YYYY hh:mm:ss'),'HH')-Date(Date#('08-03-2017 11:30:00','DD-MM-YYYY hh:mm:ss'),'HH'))

• ###### Re: Time Difference in Hours

Interval(if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 1,'< 1 hour',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 2,'< 2 hours',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 3,'< 3 hours',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 4,'< 4 hours',

'> 4 hours')))))

Where, And_allocatedDate2 is the field name.

The error is showing as ')' expected.

It will be of great help if you can guide me.

• ###### Re: Time Difference in Hours

Tried it and it worked out perfectly, however I just had to do a minor change to get the answer only in hours.

Interval(Date((now()),'HH')-Date(Date#('08-03-2017 11:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH')

This eqaution worked for me.

Thanks a lot Mate. Cheers

• ###### Re: Time Difference in Hours

Hi,

Try Something like below,

Time-Time2 as Diffrence,

If(Time-Time2<1,'<1',

If(Time-Time2<3,'<3',

If(Time-Time2<5,'<5','<=10'))) as TimeBucket;