'%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 a Fixed Date When an Excel Check Box Is Selected
Question details
The user wants to record a static date that does not update automatically when a checkbox is ticked in the worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking the exact date a task is marked as complete using a checkbox, without having the date change the next day.
- Observed behavior
- The standard =TODAY() formula updates dynamically upon reopening or recalculating the workbook, replacing the original date of completion with the current date.
Before modifying formula calculation settings, be aware that enabling iterative calculations affects how all currently open workbooks handle circular references.
Use Iterative Calculation to Freeze the Date
Enable iterative calculation to allow intentional circular references, which prevents the TODAY() function from recalculating once the checkbox is ticked and a date is generated.
By default, Excel does not allow a cell's formula to refer to its own cell (a circular reference). However, by turning on iterative calculation, you can write a formula that checks if it already contains a date. If it does, it keeps that date instead of generating a new one.
Go to File > Options. In the Excel Options dialog box, select the 'Formulas' category on the left. Check the box for 'Enable iterative calculation' and click OK.
Select the cell where you want the fixed date to appear (for example, C25). Enter the following formula: =IF(B25,IF(C25<>"",C25,TODAY()),"")
Right-click cell C25, select 'Format Cells', choose 'Date' from the Number tab, select your preferred date format, and click OK.
Check the box linked to cell B25. The current date will appear in C25 and will remain fixed even if the workbook is recalculated or reopened.
Manually Insert a Fixed Date Using Keyboard Shortcuts
If you want to avoid changing Excel's application-level calculation settings, you can manually insert a static date next to the checkbox using a built-in shortcut.
Easily Manage Checkboxes and Formulas with WPS Office
WPS Spreadsheet provides a seamless environment for managing form controls like checkboxes and complex formulas, including iterative calculations. You can easily track completion dates and manage daily tasks for free.
- 1. Enable Iteration in Settings: Open WPS Spreadsheet, click on 'Menu' in the top-left corner, go to 'Options', and select the 'Calculation' tab. Check the 'Iteration' box and click OK.
- 2. Link Your Checkbox: Insert a checkbox from the Developer tab, right-click it to access 'Format Object', and link it to a specific cell (e.g., B25).
- 3. Input the Formula: In your target date cell (e.g., C25), type the formula: =IF(B25,IF(C25<>"",C25,TODAY()),"") and press Enter.
- 4. Format the Date Cell: Right-click the target cell, select 'Format Cells', and apply a Date format. The date will now freeze upon checking the box.
Frequently Asked Questions
Why does the TODAY() function keep changing my dates?
The TODAY() function is known as a 'volatile' function. This means it constantly recalculates the current date from your system's clock every time any cell in the worksheet is modified, recalculated, or when the workbook is opened.
What is iterative calculation in Excel?
Iterative calculation is a feature that allows Excel to calculate a formula repeatedly until a specific numeric condition is met. Importantly, turning it on permits intentional 'circular references'—formulas that refer back to their own cells—which are otherwise blocked as errors.
Are there risks to enabling iterative calculation?
Yes. Enabling iterative calculation applies to all open workbooks. If you accidentally make an unintended circular reference in a different part of your spreadsheet, Excel will not display a warning error, potentially causing calculation mistakes without you noticing.
