0

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?

enter image description here

Matt
  • 466

4 Answers4

1

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.

Aganju
  • 9,982
  • 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. – Matt Jul 07 '16 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. – Aganju Jul 07 '16 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 – Matt Jul 07 '16 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. – Aganju Jul 07 '16 at 11:47
  • I only wrote this to avoid some people suggesting that I just don't see the change because Now() by default only displays the hour and minute portion of time. I simply wanted to indicate that I see the second portion as well. My problem remains that without a user manually pressing F9 or the like no automatic updates occur, neither on timers, nor through a RTD server whose updates are invoked through a timer callback and "UpdateValue". Nothing in my spreadsheet changes at all. – Matt Jul 07 '16 at 11:53
  • 1
    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. – Matt Jul 07 '16 at 12:04
0

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".

forgetaboutme
  • 1,046
  • 8
  • 9
  • I clearly stated I use Excel 2016, I even included a screen shot showing I set my calculation mode to "Automatic", downvoted. – Matt Jul 07 '16 at 10:51
  • sorry for that, I edited my answer, it is the same for Excel 2016. – forgetaboutme Jul 07 '16 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. – Matt Jul 07 '16 at 11:00
  • If you press F9, it should update. Otherwise, check out this link – forgetaboutme Jul 07 '16 at 11:04
  • I need automatic updating, not manual updating. – Matt Jul 07 '16 at 11:05
0

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?

Ross
  • 101
  • Yes it updates when I push F9, and in all previous versions it updated automatically when setting Workbook Calculation to "Automatic". Mine does not. – Matt Jul 07 '16 at 11:01
0

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.

Matt
  • 466