I set calculation mode to automatic but when I enter "=Now()" inside a cell I do not see the time updating even when formatting is changed to show seconds. That worked in prior Excel versions. Is there anything fundamental that changed?
Add a comment
|
4 Answers
The assumption is not correct, it never updated automatically, in no Excel version.
It is not really possible, as it would mean that Excel would continuously update, using 100% of the CPU and disallowing any useful user interaction.
It does update when something else (calculation relevant) is changed on the sheet, and a recalculation is triggered that way. Changing a formatting is not an event that needs a recalculation, so that is not enough; but for example typing anything in any cell will trigger a recalculation.
-
You are saying there was never a way to have Excel recalculate automatically on cells' changed values without user intervention? I disagree. I did have it in Excel 2013 and it worked perfectly well. And yes, it did use up lots of resources but it did exist. Lots of things apparently changed in Excel 2016. Same with RTD server functionality, Microsoft had to post several fixes for that alone.– MattJul 7, 2016 at 11:34
-
Feel free to disagree. i have explained why it is not possible to have it that way; but you are free to believe it anyway.– AganjuJul 7, 2016 at 11:35
-
Then why did my RTD server push data on a timer callback before but now nothing updates? And by the way I never even hinted at wanting to update upon changing a formatting. I need updating on changing values– MattJul 7, 2016 at 11:41
-
the post says: "...even when formatting is changed to show seconds...". When a value is changed, the display of a cell with NOW() does change (assuming the seconds or whatever changes is visible). If that is what you refer to, it was not clear at all. This works in Excel 2010 and 2013 (just verified); I don't have 2016, can't check.– AganjuJul 7, 2016 at 11:47
-
1I think I found the culprit. I marked the UDF I am using as volatile
[ExcelFunction(IsVolatile = true)]and now the timer callback via RTD automatically updates the worksheet without any manual user intervention.– MattJul 7, 2016 at 12:04
Make sure the following setting is set:
Excel 2016: File > Options > Formulas > Workbook Calculation > Automatic.
And then use:
F9 or Ctrl+F9
If that does not work, please check for circular reference:
Formula > Error check > Circular reference
Or try to check the format type of the calculated cell, change "Text" to "General".
-
I clearly stated I use Excel 2016, I even included a screen shot showing I set my calculation mode to "Automatic", downvoted.– MattJul 7, 2016 at 10:51
-
sorry for that, I edited my answer, it is the same for Excel 2016. Jul 7, 2016 at 10:54
-
You may misunderstand my whole question. The "Now()" function returns the current date and time. My problem is that even in Automatic calculation mode the cell does not automatically update which it should. Even setting the cell format to General does not change that. When I press F9 it does update the cell value but I need automatic updating not manual updating.– MattJul 7, 2016 at 11:00
-
-
nothing has changed, does it show the time after you enter the formula? It will only update when the sheet is recalculated, does it update when you press F9?
-
Yes it updates when I push F9, and in all previous versions it updated automatically when setting Workbook Calculation to "Automatic". Mine does not.– MattJul 7, 2016 at 11:01
I think I found the culprit. I marked the UDF I am using as volatile [ExcelFunction(IsVolatile = true)] and now the timer callback via RTD automatically updates the worksheet without any manual user intervention.