I'm having trouble creating a formula, which would calculate the average of all days between 2 date columns into one cell.

I've tried Average(days(A2:A1000,B2:B1000)) and Average (B2:B1000-A2:A1000), the second of which produces a negative result, but same number, however when I pull the data using just the Day function, calculating one row at a time and then select the set, the Average function on the bottom right corner gives me an entirely different number.

The dates are not ordered from smallest to highest, as there is a huge range between the two columns.

Thanks in advance.

1 Answer 1


So, are you trying to calculate the average of the difference between those two columns of each row?? You may do it like this:


You may change B and A for the corresponding columns. The array formula iterates the minus operation for each row and the condition ">0" excludes those cells empty or with text

  • Thank you, Martin! This was very helpful.
    – Morrigan
    Nov 5, 2022 at 15:21
  • Great! I'm glad!
    – Martín
    Nov 5, 2022 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.