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.
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.
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] "2023-06-03 13:31:01 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: 1LlZXwI7cxDUHyGbiW9ojYlARmp0mtR6-6xEUCXi-hI4
#> 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 × 2
#> datetime as_displayed
#> <dttm> <chr>
#> 1 2023-06-03 20:31:01 2023-06-03 20:31:01Note 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] "2023-06-03 20:31:01 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:
- 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 …
- 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).
- 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.
- Google Sheets use an epoch of 30 December 1899 00:00:00 UTC.
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] "2023-06-03 13:31:05 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] "2023-06-03 13:31:05 PDT"
with_tz(tt, tzone = "America/Denver")
#> [1] "2023-06-03 14:31:05 MDT"
with_tz(tt, tzone = "Etc/UTC")
#> [1] "2023-06-03 20:31:05 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
CellDataschema.
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 × 2
#> datetime serial_number
#> <dttm> <dbl>
#> 1 1899-12-30 12:00:00 0.5
#> 2 1899-12-31 18:00:00 1.751899-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
#> # of named ranges: 1
#>
#> (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 × 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 [1]> NA
#> 6 =DATE(moment_utc) <fmla [1]> NAThat’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 × 3
#> what datetime serial_number
#> <chr> <chr> <fmla>
#> 1 moment 2023-06-03 13:31:14 PDT =ARRAYFORMULA(TO_PURE_NUMBER(B2:B))
#> 2 moment_ny 2023-06-03 16:31:14 EDT NA
#> 3 moment_utc 2023-06-03 20:31:14 UTC NA
#> 4 moment_ny_utc_force 2023-06-03 16:31:14 UTC NA
#> 5 =NOW() =NOW() NA
#> 6 =DATE(moment_utc) =(DATE(2023,6,3)+time(20,31,14.7)) NARead 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 2023-06-03 20:31:14 45080.86
#> 2 moment_ny 2023-06-03 20:31:14 45080.86
#> 3 moment_utc 2023-06-03 20:31:14 45080.86
#> 4 moment_ny_utc_force 2023-06-03 16:31:14 45080.69
#> 5 =NOW() 2023-06-03 20:31:16 45080.86
#> 6 =DATE(moment_utc) 2023-06-03 20:31:14 45080.86
read_sheet(ss_ny) %>% as.data.frame()
#> ✔ Reading from "tz-america-new-york".
#> ✔ Range 'Sheet1'.
#> what datetime serial_number
#> 1 moment 2023-06-03 20:31:14 45080.86
#> 2 moment_ny 2023-06-03 20:31:14 45080.86
#> 3 moment_utc 2023-06-03 20:31:14 45080.86
#> 4 moment_ny_utc_force 2023-06-03 16:31:14 45080.69
#> 5 =NOW() 2023-06-03 16:31:17 45080.69
#> 6 =DATE(moment_utc) 2023-06-03 20:31:14 45080.86
read_sheet(ss_utc) %>% as.data.frame()
#> ✔ Reading from "tz-etc-utc".
#> ✔ Range 'Sheet1'.
#> what datetime serial_number
#> 1 moment 2023-06-03 20:31:14 45080.86
#> 2 moment_ny 2023-06-03 20:31:14 45080.86
#> 3 moment_utc 2023-06-03 20:31:14 45080.86
#> 4 moment_ny_utc_force 2023-06-03 16:31:14 45080.69
#> 5 =NOW() 2023-06-03 20:31:19 45080.86
#> 6 =DATE(moment_utc) 2023-06-03 20:31:14 45080.86Main conclusions:
- All 3 versions of
momentresult 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(forcingmoments 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' <id: 1N6TD-6vwPy3UgDS4i85_vbOw_F9IPPkMx_04lhmqW_k>
#> • 'tz-america-new-york' <id: 153iIAZ5dti6svjSRqg-4uu20x9TztyhtmJIRoravSUY>
#> • 'tz-default' <id: 16i-SjgQR5nhbxxGF-dND2otRb1v6lsK-6yYTfUSySdM>