Interval between time start and end

[marcohadiyanto]

Hi All,

i'm trying to get interval between time start and time end (StartTime) - (EndTime), my time format is 24 hours. when StartTime greater than EndTime, it's fine, but  if EndTime greater than StartTime, the interval function always doing the minimum interval.

ex 1:

StartTime : 10:00:00

EndTime : 12:00:00

interval : 2:00

---------------------------------> it's work perfect it's calculate between 10AM to 12 PM.

ex2:

StartTime : 10:00:00

EndTime : 02:00:00

interval 8:00

--------------------------------->it's Error because it's only calculate between 10AM to 2AM. the result i want is 16:00

what's expression to make this interval correctly?

regards,,

Marco

[marcohadiyanto]

"

Hi,

Try This Method

if( EndTime<StartTime,Time('24:00:00','h:mm:ss ')-(EndTime-StartTime),EndTime-StartTime)

Regards

Perumal"

hi, thanks for helping, but it's not working. when i'm trying to use Time('24:00:00','h:mm:ss ')  it show 00:00:00 not 24:00:00 that's why the result is the same like we're doing (EndTime-StartTime)

Regards,

Marco

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this expression

     =interval(if(Time(StartTime)<Time(EndTime),Today()+Time(EndTime),(Today()+1)+Time(EndTime))-(today()+Time(StartTime)),'hh:mm:ss')

Hope it helps

Celambarasan

[perumal_41]

Hi,

Sorry for Small Mistake .Now Try This Method

Time((Time('23:59:59','h:mm:ss ') -Time('8:00:00','h:mm:ss '))+Time('00:00:01','h:mm:ss '),'h:mm:ss ')

Regards

Perumal

[marcohadiyanto]

Hi Jagan,

here i give my sample data.

it's similiar like this.

Thank You,

Marco

[marcohadiyanto]

Hi Celam,

it works perfect..

Thanks you so much

[CELAMBARASAN]

Hi,

     Im just adding date to the time.if the endtime is lesser than the start time then increase the date by 1.

Celambarasan

[marcohadiyanto]

Hi,

what if i want to know sum of interval hour?

can i just add sum like this :

=sum(interval(if(Time(StartTime)<Time(EndTime),Today()+Time(EndTime),(Today()+1)+Time(EndTime))-(today()+Time(StartTime)),'hh:mm:ss'))  ?

i tried with that no error line but it the status "error in expression"

any idea?

Regards,

Marco    

[CELAMBARASAN]

Hi,

     Can you please tel me what dimensions you have used?

Celambarasan

[marcohadiyanto]

hi,

after we get the result of interval, i want to sum it. my dimension is month.

here is my expression. when i put sum on this it's error.

=Floor(num#(Interval(if(Time#(num(Only({<STAGR={'MTBJOS'}>}SMEBTR)*100,'0000'),'hhmm') <Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'),

Today()+Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'),(Today()+1)+Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'))

-(Today()+Time#(num(Only({<STAGR={'MTBJOS'}>}SMEBTR)*100,'0000'),'hhmm')),'hhmm'))/100)

Regards,

Marco

[CELAMBARASAN]

Hi,

Do you have Many start and end dates for each Months?

if so try with aggr
     =Sum(aggr(Expr,StartTime,EndTime,Month))

Hope it helps

Celambarasan