How to Hardcode a Timestamp in IF Function (Google Sheets)

Trying to compare a cell value to a specific timestamp in Google Sheets using the IF function? It’s trickier than it looks—especially when hardcoding a date and time. If you’ve run into unexpected results or errors, chances are the timestamp format isn’t being interpreted the way you think.

In this post, we’ll walk through the correct way to hardcode a timestamp inside an IF function in Google Sheets, along with working examples and how to apply it across ranges using ArrayFormula.

Table of Contents

Why Hardcoding a Timestamp in IF Might Not Work

You may assume that hardcoding a timestamp works the same across functions in Google Sheets—but it doesn’t.

For example, the COUNTIF and IF functions handle hardcoded timestamps very differently.

Let’s take a look at how COUNTIF handles it.

=COUNTIF(
  A1:A,
  ">2020-09-20 10:00:00"
)

This works perfectly. It counts all cells in column A with a timestamp greater than 20th September 2020, 10:00 AM.

Here, the hardcoded timestamp string is interpreted correctly by COUNTIF, whether it’s in ISO format (YYYY-MM-DD) or your locale-specific format (DD/MM/YYYY or MM/DD/YYYY).

However, this same direct string approach won’t work with the IF function.

Correct Way to Use a Hardcoded Timestamp in IF Function

To compare a timestamp in a cell with a fixed (hardcoded) timestamp in an IF formula, you must build the timestamp using DATE() and TIME(), or use DATEVALUE() and TIMEVALUE().

Let’s see how.

Example: Comparing a Single Cell Timestamp

Suppose cell A1 contains this datetime:

21/09/2020 14:02:56

You want to check whether this timestamp is after 20th September 2020 at 10:00 AM. Here’s how to do it:

Method 1: Using `DATE()` and `TIME()`

=IF(
  A1 > DATE(2020, 9, 20) + TIME(10, 0, 0),
  TRUE,
  FALSE
)

This returns TRUE because 21st Sept, 2:02 PM is after 20th Sept, 10:00 AM.

DATE(2020, 9, 20) creates the date part
TIME(10, 0, 0) creates the time part
The two together form a full timestamp for comparison

Method 2: Using `DATEVALUE()` and `TIMEVALUE()`

=IF(
  A1 > DATEVALUE("2020-09-20") + TIMEVALUE("10:00:00"),
  TRUE,
  FALSE
)

This gives the same result.

DATEVALUE() accepts a string-formatted date, ideally in ISO format (YYYY-MM-DD) for reliability.
TIMEVALUE() takes a time string like "10:00:00".

Apply IF Function with Timestamp to a Range

Now, let’s scale this to evaluate a range of timestamps (e.g., A1:A3 or an entire column).

Example: A1:A3 Range

=ArrayFormula(
  IF(
    A1:A3 > DATE(2020, 9, 20) + TIME(10, 0, 0),
    TRUE,
    FALSE
  )
)

IF function comparing hardcoded timestamp in Google Sheets (A2:A3 range)

Or using the alternate method:

=ArrayFormula(
  IF(
    A1:A3 > DATEVALUE("2020-09-20") + TIMEVALUE("10:00:00"),
    TRUE,
    FALSE
  )
)

Clean Version for Entire Column

If you’re applying this to an entire column (like A1:A), it’s good practice to exclude empty cells to avoid unnecessary FALSE values.

Use this version:

=ArrayFormula(
  IF(
    A1:A = "",
    ,
    IF(
      A1:A > DATE(2020, 9, 20) + TIME(10, 0, 0),
      TRUE,
      FALSE
    )
  )
)

Or:

=ArrayFormula(
  IF(
    A1:A = "",
    ,
    IF(
      A1:A > DATEVALUE("2020-09-20") + TIMEVALUE("10:00:00"),
      TRUE,
      FALSE
    )
  )
)

Summary

Task	Approach
Check single timestamp	`IF(A1 > DATE() + TIME(), TRUE, FALSE)`
Use in array	Wrap in `ArrayFormula()`
Clean for full column	Nest an `IF(A1:A = "", , ...)` to skip blanks
Use readable format	`DATEVALUE()` + `TIMEVALUE()`