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
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
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
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
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:
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
Thanks for your help Barry and SGIII. What a shame that the function is not built in. The spreadsheet from that other place has been doing it for years. Come on, Apple. Catch up.
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
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
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
SG,
TODAY is difficult to experiment with, but clearly should update when you reopen a document. I think the OP will only have a problem if he never intends to close the document overnight.
Jerry
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
I changed the document yesterday and entered the =today(). The proper date displayed. I opened the document today and today's date displayed. I'll try it with =now to see if it will keep a certain date until and unless the document is modified.
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.
After a break for coffee, a small change to the table (I entered 4 in cell B4)
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.
Yesterday I put NOW in the cell and it showed yesterday's date. Today I launched it and the cell shows today's date without my making any alteration to any cell. For this usage, then, TODAY = NOW. As an aside, one wonders why Mr. Apple doesn't put some of this info in response to a Help inquirery of "Date."
Hi Dr. Dave,
Here's the entry Formulas and Functions Help:
TODAY
The TODAY function returns the current date. The time is set to 12:00 a.m.
TODAY()
Notes
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
"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
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
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?