'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create an Automatically Updating Elapsed Days Counter in Excel
Question details
The user wants to create a formula in Excel that calculates the number of days elapsed since a specific date and updates automatically each day.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking the number of days passed since a given start date for project management, tracking, or dashboarding purposes.
- Observed behavior
- The user needs to set up the TODAY function and ensure Excel's calculation settings are properly configured to allow for daily automatic updates without manual intervention.
Ensure your starting dates are correctly formatted as standard dates (e.g., MM/DD/YYYY) in your spreadsheet before applying the calculation formulas.
Use the TODAY Function with Automatic Calculation
Use a simple subtraction formula with the TODAY function and enable automatic calculation to ensure the elapsed days count updates every time the workbook is opened.
The TODAY function in Excel returns the current date based on your system clock. By subtracting a past date from TODAY(), you get the exact number of elapsed days. For this to work seamlessly, Excel's calculation mode must be set to Automatic so the sheet recalculates upon opening.
Input your start date into a specific cell in your worksheet, for example, cell A2.
In an adjacent cell where you want the elapsed days to appear, type the formula =TODAY()-A2 and press Enter.
If the result appears as a date instead of a standard number, select the cell, go to the Home tab, click the Number Format dropdown in the ribbon, and choose 'Number' or 'General'.
Go to the Formulas tab on the ribbon, click 'Calculation Options', and select 'Automatic'. This guarantees your elapsed days counter refreshes every day you open the file.
Calculate Elapsed Days Easily with WPS Spreadsheet
WPS Spreadsheet offers full compatibility with Excel formulas, including the TODAY function. You can easily track elapsed days, manage large datasets, and ensure your numbers update daily with built-in automatic calculations.
- 1. Open your document in WPS: Launch WPS Spreadsheet and open your workbook containing the start dates you want to track.
- 2. Input the elapsed days formula: Select the target cell and enter =TODAY()-A2 (replacing A2 with the cell containing your starting date).
- 3. Verify calculation settings: Navigate to the Formulas tab, click the Calculation Options icon, and ensure 'Automatic' is checked so the date updates daily.
Frequently Asked Questions
Why is my elapsed days formula returning a date format instead of a number?
This happens when Excel automatically inherits the date formatting from the referenced cell. To fix this, select the cell with your formula, go to the Home tab, click the Number Format dropdown, and choose 'General' or 'Number'.
How can I calculate only working days instead of total elapsed days?
To calculate only business days (excluding weekends), you should use the NETWORKDAYS function instead of standard subtraction. Use the formula =NETWORKDAYS(A2, TODAY()), where A2 contains your start date.
Why isn't the TODAY function updating the days counter when I open my file?
Your workbook calculation options might be set to Manual. Go to the Formulas tab, click on Calculation Options, and change the setting to Automatic. The TODAY function will then recalculate based on the current system date every time you open or modify the file.
