3 Ways to Automatically Enter Timestamps in Google Sheets - 3 [No GAS, Possible with Functions!]

This is an introduction to the automatic timestamp feature available in Google Sheets. In addition to the standard method using GAS, I will also introduce slightly modified code for when cells are protected, as well as how to achieve it using functions.

This is the final part of a three-part series, Part 3: How to achieve it using functions without GAS.
Click here for the previous article.

Q. When I add a comment to column B of a specified sheet, I want to automatically enter a timestamp (date and time) in column A.

These are the requirements for this timestamp task.
Let's consider three approaches for this task.

1. Create a standard timestamp using GAS (onEdit)

2. Do not allow users to edit the timestamp (column A) (Trigger settings)

3. Timestamp without using GAS. (Is it possible with functions?)

So far, I have answered and explained methods 1 and 2 using GAS. This time, let's consider

3. Timestamp without using GAS. (Is it possible with functions?)

let's think about it.

A3. Timestamp without using GAS. (Is it possible with functions?)

Information online suggests that if you want to use a timestamp feature like this, it is almost always introduced as a method using VBA for Excel or GAS for Google Sheets.
Some sites even state, "Since functions that are recalculated will have their timestamps updated, it is impossible without GAS."

Is that really true?

The NOW() function is always recalculated upon updates

When implementing a timestamp with a function, the most useful one is the NOW() function. As the name suggests, this is a function that returns now (current date and time), and it is a function that has existed in Excel for a long time.

Let's try entering this.

Immediately after entering the function, the current date and time are entered into the cell, but it is true that every time you edit another cell, it is recalculated and the date and time are updated.

By the way, even if you don't edit the cell at all, it is recalculated when you reopen the spreadsheet, so the date and time are updated every time you open it.

This is a specification, so unfortunately you cannot stop automatic recalculation.

In Excel, you can switch automatic calculation to manual, but since you cannot turn off automatic calculation for specific functions only, all formulas in the workbook will stop calculating. In the first place, if you set it to manual, even the initial NOW function won't work.

If you try to implement this as a timestamp as is, for example, by entering the following formula in A3,

=IF(B3="",,NOW())

and then try updating column B with A3 autofilled downwards,

All timestamps will become the date and time when the cell was last updated.
This means it cannot be used as a timestamp.

Taboo (Forbidden Art)? Using Circular References and Iterative Calculation to Lock Calculations

So what should you do? Is it impossible?
Since it's impossible with standard methods, let's try using the forbidden art? of circular references and iterative calculation.

A circular reference is when a cell containing a formula refers to itself, or refers to a cell that contains a formula that refers back to that cell.

Simply put, it's a state like the image above where you enter =A2 in cell A2.

Since it actually exists as a feature, circular references are neither a forbidden art nor a taboo. Just because you use it, equivalent exchange won't cause you to lose a part of your body.

However, it's likely a feature that most people don't use, and perhaps due to the trauma? of being scolded by Mr. Excel, even people who are good at functions might feel resistant to using it, right?

Let's take a step forward with courage and dive into a new world.

To resolve the circular reference error, you need to turn iterative calculation ON in the settings.

Go to File > Settings > Calculation, and the screen above will open. To achieve the timestamp,

• turn on iterative calculation

• set maximum iterations to 1

and configure it.

The maximum number of iterations could be the default value in this case, but
the value looping and looping~♪
is a huge waste, so the minimum necessary is ideal.
In this case, one recalculation is enough.

Does turning on iterative calculation affect other functions?
It has absolutely no effect. Please use it with confidence.
Of course, if you are using other circular reference formulas, it will have an effect, but such cases are extremely rare.

Here is the formula to achieve timestamps using sheet functions!

After saving the settings, enter the following formula in A3 and autofill downwards.
Now you are ready.

Are you underwhelmed by how surprisingly simple the formula is?

=IF(B3="",,IF(A3>0,A3,NOW()))

This method is actually also possible in Excel.
However, the IF(A3>0 part of the formula should be IF(A3<>"" in Excel.

If you write IF(A3<>"" in Google Sheets for this part, 1899/12/30 0:00:00 gets written. It seems that even if it looks empty, it is treated as containing a 0. A difference in specifications??

Therefore, the key in Google Sheets is to determine whether a timestamp is already entered in A3 (the cell itself) by using IF(A3>0 instead.

Let's test if this really makes the timestamp function and ensures it doesn't update on its own.

The timestamp was entered correctly, and even when other cells were edited, it was not recalculated, and the date and time entered were maintained.

It works quickly even with editing multiple cells via copy and paste, and if you delete the comment, the timestamp is also reset.

Of course, the timestamp will not update even if you close and reopen the spreadsheet or reload it.

Since it is a function, there is no problem even if column A is protected, and it works smoothly even in the app version on smartphones.

Weaknesses of timestamps using sheet functions

Isn't this better than GAS? you might think, but the only requirement that cannot be met is the bold part below.

It can handle input in column B (blank → non-blank), but it cannot handle updating the timestamp when a cell that already has text is "edited".

Since the formula only determines whether the cell in column B is blank or not blank, it cannot distinguish whether the cell has been updated.

To update the timestamp by adding a comment, you must first delete the comment to reset the timestamp.

This part must be covered by operational procedures (informing users of the rules).

Can function-based timestamps be used with Arrayformula?

If timestamps can be achieved with sheet functions, you might be wondering, how about combining them with Arrayformula? That is a valid question.

Surprisingly, it can be combined with Arrayformula and works. The following formula in cell A3 works fine.

=Arrayformula(IF(B3:B="",,IF(A3:A>0,A3:A,NOW())))

I thought it might be recalculated entirely due to the latest edit, but the cells where the timestamp was already entered remained fixed and did not change. No problem there.

Although there is a limitation that the timestamp is not updated when the cell is edited, there are still many benefits, so I think timestamps implemented with functions should be more widely used.

In this verification, the operation of the timestamp using functions seemed fine, but there is still a fear that because it is displayed by a volatile function rather than a written value, it could disappear at any time. It also doesn't keep an edit history. Please

use at your own discretion and risk.

Over the course of three parts, I have answered and explained how to implement timestamps. Was this helpful?

There are other use cases that apply the circular reference and iterative calculation used this time, so I would like to cover them eventually.

■ Next article in this series