Forum Discussion

KelHill's avatar
KelHill
Copper Contributor
Nov 10, 2023

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 va...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 10, 2023

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"
  • KelHill's avatar
    KelHill
    Copper Contributor
    Nov 14, 2023
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 15, 2023

      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.

      • KelHill's avatar
        KelHill
        Copper Contributor
        Nov 15, 2023
        Hi Sergei Baklan
        I have shown the formula I used at the bottom of column F in my screencap.

Resources