As you might be aware, times and dates in Excel are basically just numbers. Having that in mind, we can use the same calculations we normally use and apply them to date and time.
In the example below, we will show how to calculate the average time between two dates.
Calculate the Average Time Between Dates
To demonstrate how to calculate the average time between dates, we first need the dates. The first thing we need to do with these dates is to order them chronologically, starting from the earliest one:
Now we need to find the difference among the dates, which can be simply done by subtracting the date in cell A3 from the date in cell A2:
1 |
=A3-A2 |
And will drag this formula till the end of our range. We will end up with the following results:
Bear in mind that the numbers shown as a difference above are shown in days. To calculate the average of these numbers, we would simply need to insert the AVERAGE formula in cell B10:
1 |
=AVERAGE(B3:B9) |
This will be our result:
To calculate the difference and average time between dates in months or years, we need to divide our numbers by 30 (for months) or by 360 (for years), and we will do this in columns C and D:
1 |
=B3/30 and =B3/360 |
We will also simply drag our average formula to the right, and end up with the following results:
Using A Simpler Formula
To achieve the same results in the same way but with a formula in one cell, we can use the following formula (located in cell F2):
1 |
=(A9-A2)/(COUNT(A2:A9)-1) |
Since each one of our dates is one after another, we can subtract the last date from the first one, and then divide this number by the total number of dates minus one.
The result of this formula will be the same as in cell B10, which is 150: