# Calculating & averaging durations

Copper Contributor

# Calculating & averaging durations

I am subracting start times (date/time) from end times (date/time) to obtain duration.

1) Any thoughts as to why the rows I've highlighted in red in the attached sample are returning incorrect values? My dataset is much larger than shown in the sample and it appears that errors only occur when the end time falls in a different month from the start time -- and yet not all records that do so are returning incorrect durations, only some of them are.

2) Can I average the resulting durations simply by adding them up and dividing them by the row count as in my formula shown at the bottom of the screencap? It's much harder to estimate what the correct answer ought to be in this case, so I'm just looking for reassurance that my formula should provide averages even when dealing with dates/times.

Thanks for any guidance or advice!

5 Replies

# Re: Calculating & averaging durations

For such sample

you may calculate duration as

``=B1-A1``

and average result.

To present values in more human friendly format it could be

``=INT(C1) & " days " & INT(MOD(C1,1)*24) & " hours " & INT(MOD(MOD(C1,1)*24,1)*60) & " min"``

# Re: Calculating & averaging durations

Thanks for the suggestion about format. The mystery I most need help with is why those durations that I have highlighted in red are incorrect.

# Re: Calculating & averaging durations

Without knowing the formula you used it's hard to answer "why". Hope suggested formula gives correct result, didn't test on all combinations.

# Re: Calculating & averaging durations

Hi Sergei Baklan
I have shown the formula I used at the bottom of column F in my screencap.

# Re: Calculating & averaging durations

This formula is for the total, not for durations in red