This article provides advice on reading and writing datetimes with Google Sheets, specifically around the matter of time zones.

A related issue is how datetimes are formatted for presentation in the Sheet itself. You can read more about these formats in the Date and time format patterns section of the Sheets API docs. At the time of writing, googlesheets4 provides no user-friendly way to address these formats, although it may do so in the future.

Attach packages and do auth

Attach googlesheets4.

library(googlesheets4)

Since we eventually create and edit Sheets, we also auth here in a hidden chunk. If you run this code, you should expect auth to happen.

The lubridate package (lubridate.tidyverse.org) is useful for this exploration, so we attach it now.

library(lubridate, warn.conflicts = FALSE)

How to work with time zones in a Google Sheet

You don’t.

Literally, you can’t.

I know this sounds very harsh, but it is the truth. Google Sheets offer essentially no support for time zones and your life will be simpler if you just make peace with this and accept that you will be looking at UTC times in Sheets.

A short demo: in R, capture the current time as tt and reveal the current time zone. Note that tt is displayed in R according to this time zone.

(tt <- Sys.time())
#> [1] "2020-04-23 12:48:57 PDT"

Sys.timezone()
#> [1] "America/Vancouver"

Write the tt datetime to a Sheet, configured with the same time zone as the local R session, and create another cell that captures the exact text presented in the browser UI for tt. Read this back into R.

dat <- tibble::tibble(
  datetime = tt,
  as_displayed = gs4_formula("=TO_TEXT(A2)")
)

(ss <- gs4_create(
  "no-time-zone-effect",
  sheets = dat,
  timeZone = Sys.timezone()
))
#> Creating new Sheet: "no-time-zone-effect"
#>   Spreadsheet name: no-time-zone-effect
#>                 ID: 1rH4nOOFBFvqCLEcYqaPjMIPj7EHfcT21tLTRL2FbHx0
#>             Locale: en_US
#>          Time zone: America/Vancouver
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>          dat: 2 x 2

read_sheet(ss)
#> Reading from "no-time-zone-effect"
#> Range "dat"
#> # A tibble: 1 x 2
#>   datetime            as_displayed       
#>   <dttm>              <chr>              
#> 1 2020-04-23 19:48:57 2020-04-23 19:48:58

Note that the tt datetime is displayed differently in Sheets than it is locally in R. Sheets presents datetimes in Coordinated Universal Time (time zone Etc/UTC), even if the Sheet’s metadata specifies a different time zone, such as America/Vancouver.

with_tz(tt, "Etc/UTC")
#> [1] "2020-04-23 19:48:57 UTC"

If you want to understand more about datetimes in R, in Sheets, and how you can sort of hack around this time zone problem, keep reading.

Need-to-know basics of datetimes

Datetimes are a complicated topic. Here we dramatically oversimplify things, in the name of making a reader who is new to all of this at least minimally functional.

The main system used to represent times in the computing world is Unix epoch time:

A moment in time is represented by the number of seconds that have elapsed since 1 January 1970 00:00:00 UTC.

The “UTC” part stands for “Coordinated Universal Time”. Yes, the order of the letters is strangely different from the words! It’s a great metaphor for this entire subject, because nothing is as simple as you’d like. Just accept it and move on. UTC is what you may already think of as “Greenwich Mean Time”, if you’ve ever encountered that term.

There are three wrinkles we must acknowledge, even when oversimplifying:

  1. Time zones. We don’t all live in Greenwich, England, so local times are described by an offset from UTC.
    • I live in Vancouver, British Columbia, which is 8 hours behind UTC (UTC−08:00). Or, at least, it is part of the year …
  2. Daylight savings time. Lots of places change their clocks twice a year. UTC does not! UTC “just is”. This means that your UTC offset is one number part of the year and another number part of the year.
    • My offset is -08:00 during standard time, but is -07:00 during daylight savings (roughly March - October).
  3. Spreadsheets are special. Spreadsheets use a different form of epoch time. Their epoch or origin is usually around 1900 and they keep track of how many days have elapsed since the epoch (not seconds, like Unix epoch time). These are sometimes called “serial dates” or “serial numbers”.
    • Google Sheets use an epoch of 30 December 1899 00:00:00 UTC.
    • Horrors I will spare you: different epochs for different spreadsheet applications (or versions thereof) and the Lotus 1-2-3 leap year bug.

You can read more in the Sheets API docs about Date/Time serial numbers.

Datetimes in R

R uses Unix epoch time.

R uses the POSIXct class to represent datetimes. (Yes, there’s also POSIXlt, but I recommend and will focus on POSIXct.)

If you ask for the current time, R prints it formatted for your time zone (or, at least, it tries). You can also ask R to reveal what it thinks your time zone is.

Sys.time()
#> [1] "2020-04-23 12:49:04 PDT"

Sys.timezone()
#> [1] "America/Vancouver"

The time zone is purely matter of display, but it’s a really nice touch! It is comforting to get a time printed by R that matches your experience of what time it is, based on looking at the clock on your wall (“clock time”).

lubridate’s with_tz() function lets you explicitly associate a datetime with a time zone, e.g. your own or any other time zone recognized by your system. And this, in turn, affects how the time is formatted for human eyeballs.

tt <- Sys.time()

with_tz(tt, tzone = "America/Vancouver")
#> [1] "2020-04-23 12:49:04 PDT"

with_tz(tt, tzone = "America/Denver")
#> [1] "2020-04-23 13:49:04 MDT"

with_tz(tt, tzone = "Etc/UTC")
#> [1] "2020-04-23 19:49:04 UTC"

Google Sheets have no ability to display a moment in time according to a specified time zone. Yes, each Sheet has an associated time zone, but this is not what it influences, even though you might expect or hope for that.

When we read datetimes out of a Google Sheet, we must:

  • Convert from days to seconds.
  • Adjust for Unix epoch versus spreadsheet epoch.
  • Use the resulting number to construct an instance of POSIXct.

When we write a datetime to a Google Sheet, we must:

  • Convert from seconds to days.
  • Adjust for the spreadsheet epoch versus Unix epoch.
  • Use the resulting number to create an instance of the CellData schema.

Datetimes in Google Sheets

Google Sheets use a spreadsheet-y version of epoch time. A datetime cell holds a so-called serial number, which is the elapsed days since the epoch of 30 December 1899 00:00:00 UTC. This number is then displayed in a more human-friendly way, according to a special token string. Currently googlesheets4 doesn’t offer any explicit support for dealing with these format strings, although one day it probably will.

Let’s gain some intuition by looking at datetimes shortly after the epoch and inspecting the underlying serial numbers. In a hidden chunk, we create a Sheet and read it into R.

#> Creating new Sheet: "near-the-epoch"
#> Reading from "near-the-epoch"
#> Range "dat"
#> # A tibble: 2 x 2
#>   datetime            serial_number
#>   <dttm>                      <dbl>
#> 1 1899-12-30 12:00:00          0.5 
#> 2 1899-12-31 18:00:00          1.75

1899-12-30 12:00:00 is noon on the day that is the Google Sheets epoch. Its underlying serial number is 0.5, because one half-day has elapsed since the epoch. 1899-12-31 18:00:00 is 6pm in the evening on the day after the epoch. Its underlying serial number is 1.75, because it’s one plus three-quarters of a day since the epoch.

Every Google Sheet has an associated time zone. It is included in the metadata returned by gs4_get(), like the locale, and is revealed by default when we print a Sheets ID.

(meta <- gs4_example("gapminder") %>%
   gs4_get())
#>   Spreadsheet name: gapminder
#>                 ID: 1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY
#>             Locale: en_US
#>          Time zone: America/Los_Angeles
#>        # of sheets: 5
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       Africa: 625 x 6
#>     Americas: 301 x 6
#>         Asia: 397 x 6
#>       Europe: 361 x 6
#>      Oceania: 25 x 6
#> 
#> (Named range): (A1 range)        
#>        canada: 'Americas'!A38:F49

meta$time_zone
#> [1] "America/Los_Angeles"

However, this time zone has a very different impact – much less impact – on the user experience than the time zone in R.

The Sheets’ time zone does not influence the display of datetimes. There is no way to request that a datetime be displayed according to a specific time zone – not via the Sheet’s time zone, not via the format string, and not via a Sheets function.

Datetimes in Google Sheets are fundamentally UTC-based and always display as such.

If you want to see “9:14 am” in your Sheet, you must make sure the serial number in that cell represents 9:14 in the morning, UTC time.

As far as I can tell, here is the only effect of a Sheet’s time zone: The formulas =NOW() and =TODAY() take the local clock time or date, according to the the Sheet’s time zone, and construct the UTC moment or date that will display as that time or date. Therefore =NOW(), especially, is almost misleading! It does not capture the current moment, in UTC, but instead fabricates a UTC moment that matches current local clock time.

This suggests various hacks if you truly, deeply want to see specific clock times in your Sheet, for non-UTC time zones.

Starting with the UTC moments, you must determine and apply the offset yourself. At a very crude level, this can be done from first principles with datetime arithmetic in the Sheet (“Vancouver is −08:00, so subtract 8 hours”). But then there’s daylight savings time and other complexities (“Except, during DST, subtract 7 hours.”). In reality, no mere mortal will ever get this right, in general. If you doubt me, please watch the YouTube video Computerphile’s “The Problem with Time & Timezones”.

You need to use external, authoritative offset information, either within R or in the Sheet. Below, we show how to do this in R. In Sheets, people tend to use Google Apps script and solutions based on moment.js.

Worked example

Let’s make all of this concrete. We construct a data frame in R with a datetime and various versions of it that explore time zone issues. We also include a couple of Google Sheet formulas, to trigger some datetime work once the data is written into a Sheet. We sketch the construction of this data frame here, with considerable abuse of notation (mixing R code and Sheets formulas):

what datetime serial_number
moment Sys.time() =ARRAYFORMULA(TO_PURE_NUMBER(B2:B))
moment_ny with_tz(moment, tzone = "America/New_York")
moment_utc with_tz(moment, tzone = "Etc/UTC")
moment_ny_force_utc force_tz(moment_ny, tzone = "Etc/UTC")
=NOW() =NOW()
=(DATE(moment_utc) + TIME(moment_utc)) =(DATE({year},{month},{day})+time({hour},{minute},{round(second, 1)})) where year, month, etc. are computed from moment_utc

Capture the current moment in time, with Sys.time(), which has no explicit time zone. Store versions of moment with explicit time zones: America/New_York and Etc/UTC. Use lubridate::force_tz() to create a new moment in time: the moment in UTC that has the same clock time as the original moment in New York.

The first Sheets formula we use is =NOW(), which you might expect to be the equivalent of R’s Sys.time(). But it’s more like force_tz(Sys.time(), tzone = "Etc/UTC"). The second formula we construct is more elaborate. It uses datetime functions in Sheets to explicitly construct moment_utc in the Sheet. The last column uses =TO_PURE_NUMBER() to reveal the underlying serial numbers for all of the datetimes.

Create 3 Sheets, with different approaches to the time zone:

  • No explicit specification of time zone. It’s hard to say what you’ll get here!
  • America/New_York
  • Etc/UTC
ss_xx  <- gs4_create("tz-default")
#> Creating new Sheet: "tz-default"
ss_ny  <- gs4_create("tz-america-new-york", timeZone = "America/New_York")
#> Creating new Sheet: "tz-america-new-york"
ss_utc <- gs4_create("tz-etc-utc", timeZone = "Etc/UTC")
#> Creating new Sheet: "tz-etc-utc"

show_timezone <- function(ss) gs4_get(ss)$time_zone

show_timezone(ss_xx)
#> [1] "Etc/GMT"
show_timezone(ss_ny)
#> [1] "America/New_York"
show_timezone(ss_utc)
#> [1] "Etc/UTC"

Capture the current moment with Sys.time(), construct the data frame described above, and write it into each of the prepared Google Sheets.

dat <- populate_sheets(Sys.time(), c(ss_xx, ss_ny, ss_utc))
#> Writing to "tz-default"
#> Writing to sheet "Sheet1"
#> Editing "tz-default"
#> Resizing one or more columns in "Sheet1"
#> Editing "tz-default"
#> Editing sheet "Sheet1"
#> Writing to "tz-america-new-york"
#> Writing to sheet "Sheet1"
#> Editing "tz-america-new-york"
#> Resizing one or more columns in "Sheet1"
#> Editing "tz-america-new-york"
#> Editing sheet "Sheet1"
#> Writing to "tz-etc-utc"
#> Writing to sheet "Sheet1"
#> Editing "tz-etc-utc"
#> Resizing one or more columns in "Sheet1"
#> Editing "tz-etc-utc"
#> Editing sheet "Sheet1"

First, let’s look at dat, the data frame we sent.

dat
#> # A tibble: 6 x 3
#>   what                datetime   serial_number                      
#>   <chr>               <list>     <fmla>                             
#> 1 moment              <dttm [1]> =ARRAYFORMULA(TO_PURE_NUMBER(B2:B))
#> 2 moment_ny           <dttm [1]> NA                                 
#> 3 moment_utc          <dttm [1]> NA                                 
#> 4 moment_ny_utc_force <dttm [1]> NA                                 
#> 5 =NOW()              <fmla>     NA                                 
#> 6 =DATE(moment_utc)   <fmla>     NA

That’s hard to parse since the datetime column is a list-column. Here’s a different look, with the most natural character representation of that column.

#> # A tibble: 6 x 3
#>   what             datetime                     serial_number                      
#>   <chr>            <chr>                        <fmla>                             
#> 1 moment           2020-04-23 12:49:20 PDT      =ARRAYFORMULA(TO_PURE_NUMBER(B2:B))
#> 2 moment_ny        2020-04-23 15:49:20 EDT      NA                                 
#> 3 moment_utc       2020-04-23 19:49:20 UTC      NA                                 
#> 4 moment_ny_utc_f… 2020-04-23 15:49:20 UTC      NA                                 
#> 5 =NOW()           =NOW()                       NA                                 
#> 6 =DATE(moment_ut… =(DATE(2020,4,23)+time(19,4… NA

Read the Sheets back into R, the Sheet with no explicit time zone set.

read_sheet(ss_xx) %>% as.data.frame()
#> Reading from "tz-default"
#> Range "Sheet1"
#>                  what            datetime serial_number
#> 1              moment 2020-04-23 19:49:20      43944.83
#> 2           moment_ny 2020-04-23 19:49:20      43944.83
#> 3          moment_utc 2020-04-23 19:49:20      43944.83
#> 4 moment_ny_utc_force 2020-04-23 15:49:20      43944.66
#> 5              =NOW() 2020-04-23 19:49:22      43944.83
#> 6   =DATE(moment_utc) 2020-04-23 19:49:20      43944.83
read_sheet(ss_ny) %>% as.data.frame()
#> Reading from "tz-america-new-york"
#> Range "Sheet1"
#>                  what            datetime serial_number
#> 1              moment 2020-04-23 19:49:20      43944.83
#> 2           moment_ny 2020-04-23 19:49:20      43944.83
#> 3          moment_utc 2020-04-23 19:49:20      43944.83
#> 4 moment_ny_utc_force 2020-04-23 15:49:20      43944.66
#> 5              =NOW() 2020-04-23 15:49:23      43944.66
#> 6   =DATE(moment_utc) 2020-04-23 19:49:20      43944.83
read_sheet(ss_utc) %>% as.data.frame()
#> Reading from "tz-etc-utc"
#> Range "Sheet1"
#>                  what            datetime serial_number
#> 1              moment 2020-04-23 19:49:20      43944.83
#> 2           moment_ny 2020-04-23 19:49:20      43944.83
#> 3          moment_utc 2020-04-23 19:49:20      43944.83
#> 4 moment_ny_utc_force 2020-04-23 15:49:20      43944.66
#> 5              =NOW() 2020-04-23 19:49:25      43944.83
#> 6   =DATE(moment_utc) 2020-04-23 19:49:20      43944.83

Main conclusions:

  • All 3 versions of moment result in the same serial number in all 3 Sheets. Lesson: in R, time zone is merely a matter of display and, in Sheets, there is only UTC.
  • moment_ny_utc_force (forcing moments NY clock time into UTC) results in the same serial number in all 3 Sheets. Lesson: If you want to see a specify clock time in the Sheet, force this on the R side, before writing to Sheets. But realize that you have fudged the datetime data in order to get the desired display.
  • =NOW() is one of the few things affected by a Sheet’s time zone (along with =TODAY(). It allows you to force the Sheet’s clock time into UTC.

Clean up.

gs4_find("tz-") %>%
  googledrive::drive_trash()
#> Files trashed:
#>   * tz-etc-utc: 1xUiNXYsk6nDMmgDq8E5FhBwJA6Zg1XCyhkuQISI91As
#>   * tz-america-new-york: 1RYjk25FKJH2QRpfT1fHGmomeO2BQ5VqErWQADLGeFJI
#>   * tz-default: 1X9Zqe--GLw6X4jajyHVb9XmKh-AiLMwZKk_g5GoJl3Y