Want to highlight a helpful answer? Upvote!

Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >

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

using Numbers, I need to time-stamp each line in a table for when it was entered

I need to time-stamp each line in a table for when it was entered.

I have a formula which is

"=IF(F18="","",NOW())"

it gives me the answer I want, but every time I edit any data in the formula is recalculates the date/time to reflect the most recent edit. I do not want to do this. I am open to any and all solutions including using a script. I do not really know anything about scripts except that they are really easy to use once it's written.

I have a formula i use in excel but it requires circular references in excel. I tried using this formula in numbers but I get the error that says I cannot reference a cell that references back to the cell i’m inputting the formula into (circular reference. It is easy to use in excel just by limiting the # of iterations in preferences. I can’t seem to find any such preference in numbers. Do you know of one? here is the formula:

"=IF(D4=”",”",IF(B4=”",NOW(),B4))"

I use it to time stamp each line in a spreadsheet. It is the only way to keep all the previous line from recalculating everytime I add a new line. If I can’t use this formula, do you know of a way to timestamp each line without having it update everytime I add a line or edit the form?

Thanks in advance- I know this isn’t exaclty an easy question but I have honestly looked everywhere 🙂

emely

MacBook, Mac OS X (10.5.8), I have numbers for mac and ipad

Posted on Feb 13, 2012 5:20 PM

Reply
Question marked as Best reply

Posted on Apr 8, 2017 3:23 AM

If one wants to be able to just single-click a cell before inserting date-time (as opposed to double-clicking) then the only way I've found is to use an Automator Service and attach a keyboard shortcut to that.


However, I'd use something like the script below in the Run AppleScript action (it isn't limited to the first table on a sheet like the script in the post above).


SG


on run

set date_ to ((current date) as string)


set the clipboard to the date_

tell application "Numbers"


activate

tell application "System Events"


keystroke "v" using {option down, shift down, command down}

end tell

end tell

end run

13 replies
Question marked as Best reply

Apr 8, 2017 3:23 AM in response to NottsDarren

If one wants to be able to just single-click a cell before inserting date-time (as opposed to double-clicking) then the only way I've found is to use an Automator Service and attach a keyboard shortcut to that.


However, I'd use something like the script below in the Run AppleScript action (it isn't limited to the first table on a sheet like the script in the post above).


SG


on run

set date_ to ((current date) as string)


set the clipboard to the date_

tell application "Numbers"


activate

tell application "System Events"


keystroke "v" using {option down, shift down, command down}

end tell

end tell

end run

Feb 13, 2012 5:36 PM in response to emely19

The best solution I've seen is to make a single cell someplace convenient with the current date which you copy and paste into the date stamp for the new data you are adding. Some people have made a single table with a pair of cells that they move to stay near the "action".


User uploaded file


The formula in B1 of the small table with the circled cell is = now(). Format this using the Cells inspector. Copy this value when every you are updaing a cell and paste (using the menu item "Edit > Paste Values").


I hope this helps.


Wayne

Feb 13, 2012 6:02 PM in response to Wayne Contello

Thank you, but it's not really a feasable solution for what i need to do. I will be using this to log all of my daughters bloodsugars and insulin doses and I would really like it to automatically stamp to date/time. I have it working perfectly in excell... I just really wanted to take advantage of numbers for ipad and the synce to icloud feature! I'm happy to use cells in a hidden column if there is a workaround....

Feb 14, 2012 12:53 AM in response to emely19

Hi Emely,


Check the Insert menu:

User uploaded file

The highlighted item is slightly misnamed, requiring a bit more than a simple selection (or keypress combination*) to act as a time stamp.


Simply choosing the menu item willproduce the result shown in A2: A time and date value is entered, but only the Date part is displayed.


Setting the cell format to Date and Time, with both parts displayed (as I've done in column B) does show both parts of the Date and Time value, but as you can see, the time part is set to midnight, as it is in any cell where you have entered only the Date part of a Date and Time value. The date was entered here in the same manner as in A3.


To enter both the current Date and current Time, the process has a few more steps:

  • Select the cell
  • Go Insert > Date & Time
  • Before confirming the entry, double-click the cell (now showing the Date) to open this small dialogue:
    User uploaded file
  • Click the pop-up menu under Choose Date format:
    User uploaded file
  • Choose the last item in the list.


Results as shown in A4 and B4 of the table above.


Regards,

Barry


•Regarding the KB shortcut shown: The Mac OS lets you define a KB shortcut for any top-level menu item. The one in the menu is a user-defined keypress combination, made to be easily remembered, not to be physically convenient to use. If doing your own, you may want to try for something eassier to get your fingers on. 😉

Feb 25, 2013 7:45 AM in response to Barry

Hmm... I can't get this to work in iWork '09 / Numbers '09. The time always comes in at 12:00, even if I have it show time and and it's 09:37am... Nothing I've been able to do makes this work.


Any sugestions for a fairly simple timestamp with time?


[SEE BELOW; PROBLEM SLOVED]

I'm leaving this up here just in case someone else makes the same mistake I was.


Message was edited by: equitek

Feb 25, 2013 7:51 AM in response to equitek

I have to totally retract my statement... 🙂 Works perfectly, I was just glazing over the "double click THE CELL" instruction to use THAT pop-up dialog... I was looking at the "inepector" I had open and at that date/time pop-up.


THANKS!


Though this does make it a few steps each time, and if you're giving a sheet to other to update, you have to train them to do it correctly, so it's not elegant by any means.

Jun 20, 2016 6:15 PM in response to emely19

Hello! I was having this problem too, so I created a work around! Hope this works for you. It unfortunately only will work on the mac desktop, not the numbers app for iOS. I assume the original writer has long since not found a use for this, but if someone else does then that's good!


My solution was to create a service and then give it a hot key.


1. Open Automator.app from your applications folder, select "service" as your type of project, then select choose.

User uploaded file

User uploaded file

2. Select "No Input" in the drop down box labeled "Service receives selected"

User uploaded file

3. Select "Numbers" in the drop down menu directly beside the previous one, or if it is not directly available, select other and select numbers in your applications folder.

User uploaded file

User uploaded file

4. Select "Library" of the left menu in the top left of the window. In the search bar type "apple"

User uploaded file

5. Drag "Run AppleScript" to the main grey area until you see a plus sign.

User uploaded file

6. You should see this.

User uploaded file

7. Copy the following text between the ++++++'s, but without copying the ++++++'s. Paste it where it says (* Your script goes here *). Make sure to leave the rest of the text that is there already (on run... return input... end run, etc).

++++++

tell application "Numbers"

set the_date to current date

activate

tell table 1 of the active sheet of document 1 to set the value of cell 1 of the selection range to (the_date as string)

end tell

++++++

You should have something that looks like this.

User uploaded file

8. Hit the hammer:

User uploaded file

Your code will compile and check that it is not in violation. If all goes well it should look like this.

User uploaded file

(if all does not go well, you are either using too old a version of automator, or your computer's operating system is too old. At the time of writing this, this was created on OSX El Capitan. If you can, update to this operating system (link here) or later and try again. If you are trying this and your operating system is so far advanced that this isn't working, I have no advice for you except why are you reading this really really old forum post??)


9. Save the file and name it what ever you like, I chose to set mine to "TimeStamp".

User uploaded file

10. Open System Preferences.

User uploaded file

11. Select Keyboard.

User uploaded file

12. Select Services from the left menu.

User uploaded file

13. Select Shortcuts from the top menu.

User uploaded file

14. Under the General Tab, select the title of your service, mine is titled "TimeStamp"

User uploaded file

15. Select the button that says "add shortcut"

User uploaded file

16. Hold down the key combination that you would like to use to activate your script.

I chose [ Shift key + Control Key + Option Key + Command Key + T Key ] as is laid out on the keyboard.

User uploaded file

(FYI: from left to right in the image the symbols actually should be read Ctrl + Alt + Shift + Cmd + T)


17. That's it! Open numbers and check that in the services fly out window that your service has it's shortcut listed next to it.

User uploaded file

Now all that's left is every time you want to add a time stamp to something, hold down shift + Ctrl + option + cmd + T and your cell will be filled with the current time. This time will be permanent, so it will not update then next time you open the document.


Hope this was what you were looking for!

using Numbers, I need to time-stamp each line in a table for when it was entered

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