Calculation Issues

Calculate Deal Age in Excel Using Close Date or TODAY

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Calculate elapsed days for each deal using its close date when present and today's date when the close date is blank.

Calculate Deal Age in Excel Using Close Date or TODAY
Product
Microsoft Excel
Device & OS
not provided
Scenario
An Excel table contains Created On and Close Date columns for open and closed deals.
Observed behavior
The desired deal age is the difference between Created On and either Close Date or today's date.
Before you start

Check that Created On and Close Date contain valid Excel dates and that open deals have blank Close Date cells. Confirm the table column names match those used in the formula.

Solution 1Recommended

Use a calculated column for deal age in Excel

Use this formula in an Excel table to calculate age for both open and closed deals.

1
Add a deal age column

In the Excel table, add a new column named Deal Age next to the existing date columns.

2
Enter the formula

In the first data cell under Deal Age, enter =IF([@[Close Date]]="",TODAY(),[@[Close Date]])-[@[Created On]] and press Enter.

3
Fill the column

Check that Excel fills the formula down the table. If it does not, copy the formula into the remaining Deal Age cells.

4
Format the result

Select the Deal Age column and choose General or Number from Home > Number Format so the results display as elapsed days rather than dates.

Use a calculated column for deal age in Excel
How the dates are chosen: The formula uses Close Date for closed deals and TODAY() for rows with a blank Close Date. Open deal ages update when the workbook recalculates.
Calculate deal age in WPS

Calculate open and closed deal age in WPS Spreadsheets

Use an IF formula in WPS Spreadsheets to subtract the creation date from the close date or today's date, then fill it down your deal list.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Identify the date columns: Find the Created On and Close Date columns. For example, use column A for Created On and column B for Close Date.
  3. 3. Enter the formula: In the first Deal Age cell, such as C2, enter =IF(B2="",TODAY(),B2)-A2 and press Enter. Adjust the cell references to match your sheet.
  4. 4. Fill and format: Drag the formula cell's fill handle down the Deal Age column, then select the results and choose General or Number from the Home tab's number format options.
Calculate elapsed days for open and closed deals in one columnOpen and edit Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and number formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why does the deal age appear as a date?

Select the Deal Age cells and choose General or Number from Home > Number Format. Subtracting dates returns a number of days, but date formatting can display that number as a date.

Will open deal ages update automatically?

TODAY() returns the current date when Excel recalculates the workbook. If values do not update, check Formulas > Calculation Options and select Automatic.

What happens when I enter a close date?

Once you enter a valid date in the Close Date cell, the formula uses that date instead of TODAY() to calculate the deal age.

What if my dates include times?

Date subtraction can return fractional days when the cells include times. To compare calendar dates only, use =INT(IF([@[Close Date]]="",TODAY(),[@[Close Date]]))-INT([@[Created On]]).