Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'))
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'))
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.
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'))
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'))
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
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.