I want a cell to take on the date and time of "this moment", and then leave it.

Contrary to some experiences I've read about, the Insert menu does have Date & Time, but it doesn't seem to respect the format I've specified for the cell. I used the Cell formatting inspector to declare the cell as Date & Time, 1/5/14, 7:08 PM. I entered text-insertion mode on the cell, and selected Insert > Date & Time. It inserted Friday, July 4, 2014 (and no time). When I then selected the cell (you know, one click, which outlines the cell, as opposed to two clicks which puts you into text insertion mode), the inspector said the cell's format had reverted to Automatic. I switched it back to Date & Time but now it reads 7/4/14 12:00 (it's really more like 1:50).

The whole thing is anti-intuitive and zany to me. Can someone tell me what's the right way to command Numbers to insert the current date and time into a cell, and then leave it? (=NOW continues to update)

UPDATE 1

The three methods I've tried for entering the current date and time into a cell are:

Enter text-insertion mode in a cell, Insert > Date & Time (which enters just the Date, as Friday, July 4, 2014), enter cell-selection mode on the cell, and change the format to Date & Time (which changes it to Friday, July 4, 2014 12:00 AM). So, not that one.

Enter text-insertion mode in a cell, type 7/4/14 1:34 pm which gets changed to 7/4/14 1:34 PM (i.e. 'pm' got capitalized), indicating that Numbers did recognize it as a date/time. This works. But it's less than satisfactory, to put it mildly.

6 Answers
6

I believe Insert > Date & Time (and its two companions) are intended for use in headers and footers. These are available after choosing File > Print. When used there, the inserted date can be clicked for a little popup formatter and button to manually reset the time to now.

The feature you want, an auto-generated NOW cell that does not change, is apparently not available.

Option A: Put the NOW function into a cell somewhere. It shows the current time. Copy the cell, then Edit > Paste Formula Results into the cell you want to have the unchanging time.

Option B: Use a general macro app (like the amazingly capable Keyboard Maestro) to generate your date/time text and paste it into a cell.

In both cases, choose the date and time formats you want on the final cell.

Insert the Date & time into your a cell in your spreadsheet. Immediately cut that cell's contents (Cmd-X) then Paste Formula Results (Cmd-Shift-V). This pastes the contents as static text, formatted as you've formatted the cell: the value will not change, even after re-opening the spreadsheet. Note that you can still change the cell formatting after pasting -- it will use the original value that you pasted, so you can "add" hours, minutes, and seconds after the fact.

it should work if you first enter the content (in your case =NOW) and then format the cell.

if you enter a formula as content it goes per default always to automatic.

If you wana now why? - It's because this. Thinking from the View of a Developer what would happen if a user adds in a formula witch generates text and not a date/time but has before assinged a Dateformate to the cell -> the Whole Application would crash so -> no go -> needs to have an other solution -> so what else maybe detect that it won't work and ask the User to change to a possible format -> what when the user sometimes does it correct (also show the Format choose dialog or not also what if he knows what hes doing, and don't want to see any dialog) -> result Dialog message is not a good solution so what else maybe doing a switch to automatic always -> its always the same so users get used to it and it works although a user doesn't know what hes doing. Only down point users have to get used to it to first enter data and then formating it. But wait shouldn't they even been used to it caused by Text/Letter Writing Applications . YES its really the same there first enter content/text then formatting it.

So i hope now it is clear why you have to first enter the formula and then second change the formate of the cell.

Whilst the first sentence is true, note that changing the format will update the formula and give the date/time that the cell was reformatted, not when the formula was initially entered. As for the rest of this answer, do you have a source for anything mentioned as it seems like a lot of irrelevant speculation which doesn't help the comprehension of a feature which is unrelated to UX as described above? Also, your generalisation that formatting after content has been edited like "writing applications" it is wildly fallacious as there is absolutely no prohibition from formatting before writing.
– grg♦Jul 4 '14 at 17:34

first as i wrote the rest after the first sentence is there if someone like me (i always need to know why a certain thing is as it is) comes to this answer. second i never did say that it is prohibited but i tried to make clear that it is in most time useless to format things until you know what all has to fit on a page (best example is a already formated 20-50 pages Word Document on which you have to insert a new Image on a certain page. It may go good but most of the time your screwed cause the whole formatting will get messed up - at least this happend to me, that's why!
– konquiJul 4 '14 at 17:46

I agree with @GeorgeGarside, but it's even worse: I put =NOW into three different cells (in three different columns). Several minutes later I changed the format in ONE of the cells, and ... all three times updated.
– ChapJul 4 '14 at 19:18

so i missunderstoud you first, i thought you were asking why it changes the format. and now your asking why it changes the time/date? - if so, then =NOW is there to have an autoupdating (time/date) but it won't be like a real watch cause on a paper you have static info so it only updates when numbers need to redraw its Window in the Screen (e.g cell format change, scrolling) or before printing. - when you just want to insert the current date/time (so it doesn't change) there are two ways. one is using the Menu Insert>Date & Time the other is to just type in the Date/Time as normal text.
– konquiJul 5 '14 at 5:46