Problem with summing time periods over 10 hours!

[snehamahaveer]

    Hi ,

I am calculating the total time spent for a particular activity by adding all the activity times over a period of a month.

The values are correct if the total is below 10 hours, how do I make sure that the addition of activity time period is correct for bigger values.?

I use, = sum(time(endtime-starttime, 'hh:mm')) to calculate. Also is it possible to divide the time by another time?

eg. = sum(time(endtime-starttime, 'hh:mm')) / Working_hours

Thanks in advance

[rubenmarin]

Hi Sneha, I don't know why time over 10 hours can be a problem, can you upload a sample to see the issue?

Anywany, can yo try this?:

Interval(Sum(endtime-starttime), 'hh:mm')

Your division for working hours can work if there is only one possible value for Working_hours:

Interval(Sum(endtime-starttime)/Working_hours, 'hh:mm')

[Gysbert_Wassenaar]

Try using the interval function instead of time. Time shows the time representation of a value, not the duration like interval does.

[snehamahaveer]

Hi Ruben and Gysbert,

Thanks for the reply, I did try the formula using the interval function but it made no difference to the calculation.

The value is still displayed as 17:07:52 instead of 81:00:55.

=sum(time(endtime-starttime, 'hh:mm'))  and

=interval(sum(endtime-starttime), 'hh:mm')        return the same incorrect value.

[jonathandienst]

Have you got nulls in either of those fields? Then you would need:

=interval(sum(Alt(endtime,0) - Alt(starttime, 0)), 'hh:mm')  

[sasiparupudi1]

may be you can try

Interval(Sum(endtime-starttime), 'm')/1440 for days

Interval(Sum(endtime-starttime), 'm')/60 for hours

Interval(Sum(endtime-starttime), 'D hh:mm')/60 for days hours minutes

HTH

Sasi

[rubenmarin]

Can be provoked by many things, please upload a sample to check.

[snehamahaveer]

Hi,

Please find attached a sample of my data. If I calculate the values on Excel it is different.

[snehamahaveer]

Jonathan,

The same issue persists even after using the Alt function.

Could you please elaborate the use of Alt function? does it remove the null values present??

Please view my sample data, it does not include null values.

[sasiparupudi1]

Hi

I do not see endtime ,starttime fields in the excel document?

Sasi