From my perspective, we can insert three types of timestamps or datetime into Google Sheets according to their behavior: dynamic, static, and conditional static.

Dynamic:

The auto-updating (dynamic) timestamp updates every time you edit your Sheet or when you close and reopen the Sheet. We use the built-in NOW function for this.

Static:

The static datetime does not refresh or update when you modify your sheet. We use a shortcut for this.

Conditional Static:

The remaining is inserting a static conditional timestamp in Google Sheets.

In this third type, we use a Lambda formula to automatically insert a static datetime in one cell when we enter a value in one another cell.

It will only update if the so-called ‘another’ cell updates. No other changes affect it.

Dynamic Datetime and the NOW Function

You can use the NOW worksheet function in Google Sheets to insert a timestamp that updates on every edit.

Just key in =now() in cell A1 to insert the current date and time in that cell. It will recalculate and refresh every time you edit your Sheet.

What are the other purposes of it?

We can use the NOW function in logical tests. For example, assume cells B4 and C4 have a task start and end datetime, respectively.

We can use =now()-B4 and =C4-now() to get the elapsed and remaining durations in cells E4 and F4, respectively.

You can find one real-life use of the NOW function in my countdown timer tutorial.

Insert a Static Timestamp Using Shortcut in Google Sheets

The best way to insert a static timestamp is by using keyboard shortcuts in Google Sheets.

It depends on which OS platform you are in. In Windows, you can use Ctrl + Alt + Shift +; key combination to insert a static datetime in Google Sheets.

If you are using Mac, then use the Command + Option + Shift +; key combination. In my test, it didn’t work. Google Documentation says, “Some shortcuts might not work for all languages or keyboards.”

How to insert a static date and time in a cell in Google Sheets?

We will consider cell A1.

Go to cell A1 and apply either of the above shortcut keys, depending on whether you use Windows or Mac. Check the formula bar. You can see A1 doesn’t contain any formula.

Insert a Static Conditional Timestamp Using a Formula in Google Sheets

Earlier, we must use Google Apps Script for this. Now, we can use a Lambda formula to limit the dynamism (volatility) of the NOW function.

I followed this approach in one of my tutorials to restrict chosen random names from refreshing in Google Sheets.

In that, I’ve used the RANDBETWEEN volatile function (NOW is also a volatile function) and controlled its volatility with the help of a Lambda. We will follow that approach here.

I want to insert a timestamp in cell A1 when I enter a value in cell B1 and remain the datetime the same until I make any changes in A1.

How do we do that?

Insert the following Lambda formula in cell A1. You can copy it down as far as you want.

=lambda(timestamp,if(B1<>"",timestamp,iferror(1/0)))(now())

Then enter any text or number (any value) in cell B1. The formula will insert the current date and time in cell A1. It won’t refresh when you edit any other cells.

NOW in Lambda (Anatomay of the Formula)

There are two parts in the above formula that inserts a static conditional timestamp in cell A1 Google Sheets: The logical part and the Lambda part.

Logical Part

Lambda performs the below IF logical test to insert a static timestamp in cell A1.

=if(B1<>"",now(),iferror(1/0))

IF Syntax: IF(logical_expression, value_if_true, value_if_false)

logical_expression: B1<>""

value_if_true: now()

value_if_false: iferror(1/0)

You can specify double quotes ("") instead of iferror(1/0) in value_if_false part to return blank. That is the most common approach.

But even if A1 seems to be blank when B1 is blank, the following test will evaluate to FALSE when it should be TRUE.

=isblank(A1)

On the other hand, the iferror(1/0) returns a pure blank that ISBLANK evaluates to TRUE.

The above IF logical part can insert a timestamp in cell A1 when you insert a value in B1. But it’s volatile and keeps on changing with each edit in Sheets. The Lambda solves that.

Lambda Part

I’ve used Lambda to make the inserted date and time static and update only when I edit B1.

Lambda Syntax:

=LAMBDA([name, ...],formula_expression)(function_call, ...)

name: timestamp. It is an identifier to pass now() to the function.

formula_expression: Above logical part after replacing now() with timestamp.

function_call: now()

Insert a Static Timestamp When Multiple Conditions are Met

How do I insert a timestamp when more than one condition is met in Google Sheets?

For example, say you want to insert a static date and time into cell A2 when B2:D2 is populated.

The following Lambda formula will do that.

=lambda(timestamp,if(and(B2<>"",C2<>"",D2<>""),timestamp,iferror(1/0)))(now())

How to use it?

Insert it in cell A2 and drag it down as far as you want. Then start entering values in B2:D.

You may replace AND logical operator with OR when you want to meet either of the conditions. Say insert a timestamp in A2 if B2, C2, or D2 has values.