Time Difference in Hours

[chanin7893]

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.

Please help me rectify the error in the equation.

(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

Thanks in advance.

[hemachandran]

hi,

Please try this set analysis

=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'))

[Silambarasan1306]

Hi,

Try Something like below,

Load *,

     Time-Time2 as Diffrence,

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

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

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

Load

     Hour(TimeDimension) as Time2

     Hour(Now()) as Time,

     From ....****;

Instead of maketime, you can use you time dimension.

[Silambarasan1306 · Accepted solution]

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'))

[hemachandran]

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'))

[chanin7893]

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')

Just added ,'HH' and the answer came in hours.

This eqaution worked for me.

Thanks a lot Mate. Cheers

[chanin7893]

Hi, Now can you please help me with this equation

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.