Calculate Deal Age in Excel Using Close Date or TODAY
Question details
Calculate elapsed days for each deal using its close date when present and today's date when the close date is blank.

- 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.
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.
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.
In the Excel table, add a new column named Deal Age next to the existing date columns.
In the first data cell under Deal Age, enter =IF([@[Close Date]]="",TODAY(),[@[Close Date]])-[@[Created On]] and press Enter.
Check that Excel fills the formula down the table. If it does not, copy the formula into the remaining Deal Age cells.
Select the Deal Age column and choose General or Number from Home > Number Format so the results display as elapsed days rather than dates.

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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. 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. 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.

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]]).




