Determine average processing time

[Anonymous]

Hello everybody,

This question is probably easy to answer for most, but I am currently in despair.

In column A is the one-time order number. I have in column B a date (end of processing), and in column C (start of processing) also. The table is e.g. 10 lines long. I want to get the average of the processing time across all lines and render it as a time format (for example, dd: hh: mm: ss).

Currently the formula looks like this:

(Sum ([start of processing]) - Sum ([end of processing])) / Count ([order number])

Although I get a result, but in comparison with Excel that does not fit.

Does anyone have an idea why this might be? I had tried it with avg, but the result is 0.

Thank you very much and greetings

K.

[sunny_talwar]

Would you be able to share some sample data to check this out? Also, share the expected output

[Anonymous]

Hi Sunny,

for example:

Timestamp A (TT.MM.YYYY hh:mm:ss)    23.02.2018 14:05:49

Timestamp B (TT.MM.YYYY hh:mm:ss)    22.03.2018 09.35:07

Moreover, I'd like to deduct the weekends and resting hours (working ours from 6am to 8pm).

So, I get the result of (tt:hh:mm:ss) 19:09:29:18 (when I calculate myself)

I tried pretty much everything, but it's not working at all. Do you have any idea?

[boorgura]

Not the cleanest approach - but something to start with:

=NetWorkDays(date(ceil(Start)),  date(floor(End))) & ' ' &

Time(

DayEnd(Start, 0, '20:00:01') - Start

+

End - DayStart(End, 0, '06:00:00')

, 'hh:mm:ss')