📌 Stage 3 · Pipeline & Forecast · Sales Pipeline CRM · Aging and Cycle Time · 35–45 min

Dates are numbers - subtract them

Learning goals

  • Compute days open with date arithmetic and a blank-check IF
  • Freeze aging at the close date for closed deals, at the aging date for open ones
  • Derive the average won sales cycle and know what it is used for

Concepts

Dates are serial numbers

Excel stores every date as a number: Jan 1, 2026 is 46023, Jun 30, 2026 is 46203, and the difference is 182 days. That is why subtracting two dates is legitimate arithmetic - and why 'Apr 14' minus nothing is an error. The mmm d, yyyy display is a costume over the number, which also means you can add 30 to a date (NET 30, Lesson 17) or average date differences (sales cycle, below).

=IF($L11<>"",$L11-$F11,Settings!$C$6-$F11)

OPP-107's days open. If the Close Date (L11) holds a date, age = close date minus created date. If it is still blank, age = the fixed aging date (Settings!C6, Jun 30, 2026) minus created date. Result here: 77 days - created Apr 14, 2026, still open at the aging date.

💡 Tips:

  • A blank cell compared with "" reads as equal - that is the standard Excel test for 'nothing entered yet', and it keeps open rows from erroring.

Aging with a purpose, cycles with a denominator

Days open exposes the deals going quiet: OPP-107 has been alive 77 days (fine for a hotel renewal - they take a season), OPP-110 sits at 31 days in Qualified with a site visit overdue (Lesson 12 will flag it), and OPP-112 is 6 days old (a baby). The same column answers a different question for closed deals - how long did winning take? The five won deals took 70, 37, 98, 47, and 24 days, an average of 55.2 days. That is Northgate's sales cycle, and it calibrates promises: quote 'six to nine weeks' to a new prospect, not 'next Friday'.

AVERAGEIFS does the won-only averaging on the Dashboard; you will write it in Lesson 20. The pattern to remember is averaging a derived column (Days Open) filtered by a criterion (Stage = Won).

💡 Tips:

  • The 98-day Cedar Hollow deal explains itself in the Companies notes: committee sign-off above $10,000. Long cycles often have a policy, not a person, at the bottom of them.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Opportunities.

  1. Type the aging formula — M11 is blank. Click it and type =IF($L11<>"",$L11-$F11,Settings!$C$6-$F11) then Enter. It returns 77 - OPP-107, created Apr 14, 2026, aged against Jun 30, 2026 because column L is empty.
  2. See a frozen cycle — M5 (OPP-101, Won) shows 70: created Jan 9, closed Mar 20 - the aging formula took the first branch and used the close date, so this number never changes again.
  3. See a newborn — M16 (OPP-112) shows 6 - created Jun 24, 2026, six days old at the aging date. Aging is only scary on deals old enough to know better.
  4. Preview the cycle average — The won rows (5, 6, 7, 9, 10) show 70, 37, 98, 47, 24 - average 55.2 days. Lesson 20 computes exactly that with AVERAGEIFS.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ M11 returned 77 (open deal aged at Jun 30, 2026)
  • ☐ Confirmed M5 = 70 freezes at the close date for the won OPP-101
  • ☐ Hand-averaged the five won cycles to 55.2 days and can explain the two-branch IF