excelisfun - Excel Magic Trick 1390: Average Number of Days Between Dates in a Single Column 3 Examples

Short Summary

Main recommendation: To find the average number of days between consecutive dates in a single column, calculate each gap between neighboring dates and then average those gaps, using either a helper column or a single array formula. The video shows three approaches: a helper column with AVERAGE, a single-cell array formula entered with Control+Shift+Enter, and a non-array method using SUM and COUNT.

Key Points

• The dates are a sequential list, so each date is compared to the one directly above it, not to a separate end or begin column.
• Helper-column method: subtract the earlier date from the later date in each row, then take the AVERAGE of those differences (the video's result is 3.2 days).
• Single-cell array method: AVERAGE can take an array of later dates minus the same array staggered one row up, which simulates the helper column.
• Non-array method: divide (SUM of later dates minus SUM of earlier dates) by the count of gaps, which avoids Control+Shift+Enter and a helper column.
• Array formulas can slow down noticeably on very large data sets, according to the speaker, who notes he did not actually time this and it is a suspicion rather than a tested fact.

Details Worth Noting

• Concrete instructions:
  • Helper column: in the second cell, enter a formula that subtracts the cell above from the current cell (later date minus earlier date), press Control+Enter, then copy it down. Then use AVERAGE on the difference column.
  • Single-cell array: enter =AVERAGE(later-date range minus the same range shifted up one row), for example B11 minus B10 and so on. Press F9 after selecting the argument to verify the simulated helper column, then press Control+Z to undo that check. Enter the formula with Control+Shift+Enter and confirm Excel adds curly brackets to the formula bar.
  • Non-array: type =(SUM(later-date range) - SUM(earlier-date range)) / (COUNT of dates - 1), using parentheses so subtraction happens first, then press Enter.
  • If the result shows an unexpected date format, select General number formatting from the Number group, or press Control+Shift+Grave Accent (Tilde).
• The helper column method is easiest to check visually, since each gap is shown separately.
• The count in the non-array method is one less than the number of dates, because there is one fewer gap than dates.

Bottom Line

Any of the three methods gives the same average gap for a single column of sequential dates; choose the helper column for transparency, the array formula for a compact single cell, or the SUM-based formula to avoid array entry.