Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

No automatic date?

I want a cell to show today's date whenever I modify any cell on the spreadsheet. Can't Numbers 3.1 do that? Help is no help. Thanks

Posted on Feb 24, 2014 2:48 PM

Reply
Question marked as Best reply

Posted on Feb 24, 2014 10:17 PM

Hi Dr. Dave,


=TODAY() will return the current date whenever the table is recalculated.


Any recalculation the next day will change the value in that cell.


If you want a stable date in the cell, use Insert (menu)> Date and Time. Despite the name, the initial use inserts the current date, with the time part of that Date and Time value set to 00:00:00. Although there is no keyboard shortcut assigned to this menu item, you should be able to define one, as the item is at top level in the Insert menu (ie. not in a sub-menu).


The insertion can also be done with an AppleScript, using a menu choice, or a Service, using either a menu choice of a keyboard shortcut. Either of these should give you more control over what is entered. SGIII may post with more details on that path.


Regards,

Barry

16 replies
Question marked as Best reply

Feb 24, 2014 10:17 PM in response to Dr. Dave

Hi Dr. Dave,


=TODAY() will return the current date whenever the table is recalculated.


Any recalculation the next day will change the value in that cell.


If you want a stable date in the cell, use Insert (menu)> Date and Time. Despite the name, the initial use inserts the current date, with the time part of that Date and Time value set to 00:00:00. Although there is no keyboard shortcut assigned to this menu item, you should be able to define one, as the item is at top level in the Insert menu (ie. not in a sub-menu).


The insertion can also be done with an AppleScript, using a menu choice, or a Service, using either a menu choice of a keyboard shortcut. Either of these should give you more control over what is entered. SGIII may post with more details on that path.


Regards,

Barry

Feb 25, 2014 4:36 AM in response to Dr. Dave

If you're looking for a date stamp, Numbers 3 does not (yet) have a menu pick to insert Date & Time. But as Barry mentions it's easy to add a date stamp to your Services Menu (to which you can attach a keyboard shortcut in System Preferences > Keyboard > Shortcuts) that looks something like this:


User uploaded file



To install it in your menu doubleclick the .workflow package after downloading this (Dropbox download). You may have to go to System Preferences > Security & Privacy and click 'download anyway'. You can inspect what's in the script (it's a very short AppleScript) by opening the workflow in Automator. And you can remove it anytime by going to your Library > Services folder (option Go in Finder menu) and trashing it the way you would any other item in Finder.


SG

Feb 25, 2014 10:16 AM in response to Dr. Dave

What a shame that the function is not built in.


Exactly which "the function" are you referring to here? And why does having it "built in" make it easier to use?


(I use Excel a lot and I'm not aware of how, in this particular aspect, you can do things more easily in Excel than you can in Numbers. A Services menu pick is like any other menu pick and can easily be assigned a keyboard shortcut).


SG

Feb 25, 2014 11:48 AM in response to Dr. Dave

Dr. Dave wrote:


I want a cell to show today's date whenever I modify any cell on the spreadsheet. Can't Numbers 3.1 do that? Help is no help. Thanks


Dave,


Getting Version 3 to do that is much more difficult than if was in Version 2. Version 2 would update if you just nudged the document, whereas Version three requires an earthquake. We just had an extensive discussion in another thread on what it takes to force a recalculation in Version 3. The concensus, I believe, was that you need to alter a dependency that involves the function that you want to update. So, pick up the computer and shake it. 😝 (no, don't do that)


I found that you can Copy the cell with the TODAY function and Paste it back in. That seems to do the trick.


Jerry

Feb 25, 2014 12:42 PM in response to Jerrold Green1

Hi Jerry,


I *think* the recalculation behavior is different with TODAY and NOW than it is with RAND. RAND clearly seems to require a dependency to force a recalculation, and that is arguably a good thing.


But NOW updates immediately here when I make a change elsewhere in the document, with no dependencies. No earthquakes needed.


I assume, though I haven't tested it, that TODAY will change when I make a change anywhere and the date has changed in the meantime. If the date is still the same as when the document was last changed, then TODAY is steady as a rock, as (I think) it should be.


Will be interested to see whether this fits with your observations.


I don't think that in this particular aspect Numbers 3 is deficient or harder to use than Numbers 2 or Excel, but I could be wrong.


SG

Feb 26, 2014 8:53 AM in response to Jerrold Green1

To satisfy my curiosity, I left a document open overnight with TODAY in a cell. Today the date was still yesterday's. Then I made a change an another part of the document (no dependencies) and it updated immediately to today's date. So I think it's working the way it's supposed to. I was always a little hazy on the difference between NOW and TODAY.


SG

Feb 27, 2014 4:35 AM in response to Dr. Dave

Hi Dr. Dave,


I'll try it with =now to see if it will keep a certain date until and unless the document is modified.


B2 contains the formula =NOW

B3 is copied from B2 then Edit > Paste Formula Results.

User uploaded file


After a break for coffee, a small change to the table (I entered 4 in cell B4)


User uploaded file


B2, =NOW has updated to 11:25 pm.

B3, Paste Formula Results is fixed at 11:18 pm.


I believe that TODAY also behaves that way, but I won't wait until tomorrow to see. 🙂


In Numbers 3.1 any change anywhere in a document (in any cell on any table on any sheet) makes every NOW update.


Regards,

Ian.

Feb 27, 2014 7:49 AM in response to Dr. Dave

Hi Dr. Dave,


Here's the entry Formulas and Functions Help:



User uploaded file

TODAY

The TODAY function returns the current date. The time is set to 12:00 a.m.

TODAY()

Notes

  • The TODAY function does not have any arguments. However, you must include the parentheses.
  • The displayed date is updated every time you open or modify your file.
  • You can use the NOW function to get the current date and time and format the cell to display both.

One difference I've noticed is that the time inserted by TODAY is always 12:00:00 AM whereas NOW inserts the current time.


SG

Feb 27, 2014 12:25 PM in response to SGIII

"One difference I've noticed is that the time inserted by TODAY is always 12:00:00 AM whereas NOW inserts the current time."


Logical behaviour, which follows the pattern set by manual entry of Date and Time values.


If only date information is entered, the time part of the Date and Time value is set to 00:00:00—midnight, at the beginning of that day.

If the full Date and Time value is entered, then both aprts are as entered.

If only the time part is entered, the date part is set to the current date.


TODAY 'enters' only the date part.

NOW 'enters' the full D&T value.

I don't think there is a function that 'enters' only the time part. If there is, the effect would be the same as that for NOW.


Regarding the overall question, SGIII's Service option seems the most useful, allowing insertion of a stable Date and Time stamp in a selected cell through a keystroke combination (shift-command-T worked on my Mac, and seemed an easy one to remember). Copy/Paste from a cell containing either TODAY or NOW, suggested above by Ian, is the method I've used previously when needed, but I suspect that, requiring more steps and a varying amount of mouse navigation to travel to and from the 'well', it would be less reliable in the OP's case.


Devon Word Service contains services to insert long and short dates and long and short date and time strings, but these work only in a text environment, and do not appear on the Numbers > Services menu unless the insertion point is in a Text box. (Checked in N 2.3, OS X v10.8.5)


Regards,

Barry

Feb 27, 2014 12:37 PM in response to Barry

Hi Barry,


Just a further bit of Date&Time trivia....


Numbers 2.3 has a menu item, Insert > Date & Time. If inserted into a Table cell, it places static text indicating the current date and 00:00 hours, which can be formatted as any Date value. That same menu item, if used to write the date and time into a Text field inserts a true date/time token which can be formatted and updated by Right-Clicking it, and it does include the current time.


Jerry

Feb 27, 2014 3:13 PM in response to Jerrold Green1

Hi Jerry,


Further exploration:


Insert > Date and Time, inserted into a cell formatted as Date and Time did include the current time on at least one test I did.


Insert > Date & Time

Select cell, then click again to select the visible date part. (This click selected the full "Thursday, 27 February, 2014" visible in the cell.)

Right click to open the contextual menu. Choose Edit Date & Time.

In the pop-up menu, note that the time of the insertion is shown in the choices including the time part.

Choose one of the time only displays.

The cell contents (not yet confirmed) change to show the time (only) of the insertion.

Click outside the cell to move the focus and confirm the entry.

The entry is confirmed (including time of day) and displayed according to the format set for the cell.


Barry

No automatic date?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.