Calculating & averaging durations

[KelHill]

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!

 

[#2]

@KelHill 

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"

[#3]

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.

[#4]

@KelHill 

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

[#5]

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

[#6]

@KelHill 

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