'%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)
Fix Excel Timestamp Cells Changing Unexpectedly with NOW and Power Query
Question details
The user is experiencing an issue where timestamp cells created with the NOW function and iterative formulas change unexpectedly during data refreshes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Refreshing data using Power Query or recalculating dependent formulas in a workbook.
- Observed behavior
- The supposedly static timestamp cells update to the current time instead of maintaining their original captured values.
Before making changes to calculation options or Power Query settings, save a copy of your current workbook to ensure you do not lose any data due to unintended formula recalculations.
Verify and Adjust Iterative Calculation Settings
Ensure that iterative calculations are properly enabled so that the circular reference used to freeze the NOW() function functions correctly.
Static timestamps using the NOW() function rely on circular references. If iterative calculation is disabled, Excel cannot process the circular reference properly, leading to either error prompts or unexpected recalculations.
Click on 'File' in the top left corner, then select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on 'Formulas' in the left-hand sidebar.
Under the 'Calculation options' section, check the box for 'Enable iterative calculation'. Ensure the 'Maximum Iterations' is set to 1.
Click 'OK' to save your changes and test the timestamp formula again.
Disable Background Refresh for Power Query
Prevent Power Query from refreshing asynchronously, which can trigger volatile functions like NOW() to recalculate unexpectedly.
Seek Help in Microsoft Tech Community
For highly complex interactions between Power Query and iterative formulas, consult experts with a sanitized version of your file.
Use WPS Spreadsheet for Stable Static Timestamps
To avoid the unreliability of iterative formulas and volatile functions like NOW(), you can use WPS Spreadsheet's built-in quick shortcuts to easily insert static timestamps that never change during data refreshes.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing data file.
- 2. Select the Target Cell: Click on the specific cell where you want to insert a permanent timestamp.
- 3. Use the Timestamp Shortcut: Press 'Ctrl + ;' to insert the current date, or 'Ctrl + Shift + ;' to insert the current time.
- 4. Confirm the Value: Press Enter. The cell now contains a static value rather than a formula, guaranteeing it will never change during refreshes.
Frequently Asked Questions
Why does the NOW() function constantly update in Excel?
NOW() is a volatile function. This means it is designed to automatically recalculate and update to the current system time whenever any change is made in the workbook or when data is refreshed.
Can I freeze a NOW() timestamp without iterative calculations?
Yes, but it requires manual intervention. You can copy the cell containing the NOW() formula and use 'Paste Special > Values' over the same cell to convert the formula into a static text/number value.
Does Power Query background refresh affect iterative calculations?
Yes. When Power Query refreshes data in the background, it can trigger asynchronous workbook recalculations. This process can cause iterative circular references containing volatile functions to trigger unexpectedly.
