Previous 1 2 Next 16 Replies Latest reply: Feb 27, 2014 5:25 PM by Jerrold Green1
Dr. Dave Level 1 Level 1 (145 points)

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

  • Barry Level 7 Level 7 (29,215 points)

    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

  • SGIII Level 5 Level 5 (5,760 points)

    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:

     

    service-today-date.png

     

     

    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

  • Dr. Dave Level 1 Level 1 (145 points)

    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.

  • SGIII Level 5 Level 5 (5,760 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • SGIII Level 5 Level 5 (5,760 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • SGIII Level 5 Level 5 (5,760 points)

    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

  • Dr. Dave Level 1 Level 1 (145 points)

    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.

  • Yellowbox Level 5 Level 5 (7,025 points)

    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.

    Screen Shot 2014-02-27 at 11.19.04 pm.png

     

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

     

    Screen Shot 2014-02-27 at 11.25.48 pm.png

     

    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.

  • Dr. Dave Level 1 Level 1 (145 points)

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

  • SGIII Level 5 Level 5 (5,760 points)

    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

    • 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

  • Barry Level 7 Level 7 (29,215 points)

    "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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

Previous 1 2 Next