Remote Ops

Convert UTC Handoff Times to Local Time in Google Sheets

How do I convert UTC handoff timestamps to local time in Google Sheets while accounting for daylight saving time?

Use LOCAL_TIME(isoTimestamp, timeZone) with explicit UTC timestamp text and a named destination zone. Keep the UTC source next to the local result: during the repeated fall hour, different instants can display the same local time.

What you need

  • Google Sheets with access to Extensions > Apps Script
  • Apps Script V8 runtime
  • Explicit UTC timestamp text and destination IANA time-zone identifiers

How this script runs

  1. A cell calls LOCAL_TIME with a timestamp and time zone
  2. The custom function parses the timestamp
  3. It formats the time in that zone, applying daylight saving
  4. The local time appears in the cell as text

Use =LOCAL_TIME(B2,C2) with the custom function below to turn UTC handoff timestamps in Google Sheets into local time for a zone such as America/New_York. It calls Utilities.formatDate and returns local date-and-time text with daylight saving applied. Keep the UTC column next to that text: different instants can have the same local clock reading.

How should UTC handoff timestamps be stored in Google Sheets?

Store each handoff’s explicit UTC timestamp as plain text, with its destination IANA time-zone identifier in the next column.

Set up a Handoffs sheet using these fictional project labels. Set column B to Plain text before pasting; otherwise, the function may receive date values instead of strings. Google’s custom-function documentation explains how cell types become script arguments. The Z at the end of each timestamp marks it as UTC.

A: Handoff B: UTC timestamp C: Time zone
Moonpickle before DST 2025-03-08T15:00:00Z America/New_York
Moonpickle after DST 2025-03-09T15:00:00Z America/New_York
Starbadger before jump 2025-03-09T09:30:00Z America/Los_Angeles
Starbadger after jump 2025-03-09T10:30:00Z America/Los_Angeles
Cloudmarmot summer 2025-07-01T16:00:00Z America/Phoenix
Cloudmarmot evening 2025-07-02T02:00:00Z America/Los_Angeles
Moonpickle first repeat 2025-11-02T05:30:00Z America/New_York
Moonpickle second repeat 2025-11-02T06:30:00Z America/New_York

Every timestamp needs seconds and must end in Z or +00:00. One to three fractional-second digits are also allowed, as in 2025-03-09T15:00:00.250Z, though the output shows whole seconds. This version rejects a timestamp if its UTC marker is missing or its offset is nonzero.

Leave the destination zone visible next to the local result. In an async decision log with an explicit response deadline, make UTC the authoritative deadline. Label each reader’s local display with the zone it uses.

How do you install the LOCAL_TIME custom function?

Add LOCAL_TIME to the spreadsheet’s Apps Script project, then pass it a timestamp cell and a time-zone cell in a sheet formula.

Google’s custom-function instructions cover the editor, saving scripts, and calling them from cells. For this sheet, use the following steps:

  1. Go to Extensions > Apps Script.
  2. In Code.gs, replace the starter myFunction without removing any unrelated scripts.
  3. Paste all the code below, then save.
  4. Go back to Handoffs and type Local handoff time in D1.
  5. In D2, enter =LOCAL_TIME(B2,C2).
  6. Fill the formula down from D2 through D9.

Run the function through the sheet formula rather than the editor’s Run button. The formula supplies the two arguments the code requires.

/**
 * Converts explicit UTC timestamp text to local date-and-time text.
 *
 * @param {string} isoTimestamp UTC ISO text ending in Z or +00:00.
 * @param {string} timeZone IANA zone, such as America/New_York.
 * @return {string} Local text in yyyy-MM-dd HH:mm:ss format.
 * @customfunction
 */
function LOCAL_TIME(isoTimestamp, timeZone) {
  if (Array.isArray(isoTimestamp) || Array.isArray(timeZone)) {
    throw new Error("Pass one timestamp cell and one time-zone cell.");
  }

  if (
    isoTimestamp === "" ||
    isoTimestamp === null ||
    isoTimestamp === undefined ||
    timeZone === "" ||
    timeZone === null ||
    timeZone === undefined
  ) {
    return "";
  }

  if (typeof isoTimestamp !== "string") {
    throw new Error(
      "UTC timestamp must be plain text, such as 2025-03-09T15:00:00Z."
    );
  }

  if (typeof timeZone !== "string") {
    throw new Error("Time zone must be IANA text, such as America/New_York.");
  }

  const text = isoTimestamp.trim();
  const zone = timeZone.trim();

  if (text === "" || zone === "") {
    return "";
  }

  const match = text.match(
    /^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:\.(\d{1,3}))?(?:Z|\+00:00)$/
  );

  if (!match) {
    throw new Error(
      "Use UTC ISO text with seconds and Z or +00:00."
    );
  }

  const instant = new Date(text);
  const valid =
    !Number.isNaN(instant.getTime()) &&
    instant.getUTCFullYear() === Number(match[1]) &&
    instant.getUTCMonth() + 1 === Number(match[2]) &&
    instant.getUTCDate() === Number(match[3]) &&
    instant.getUTCHours() === Number(match[4]) &&
    instant.getUTCMinutes() === Number(match[5]) &&
    instant.getUTCSeconds() === Number(match[6]);

  if (!valid) {
    throw new Error("UTC timestamp contains an invalid date or time.");
  }

  if (zone !== "UTC" && !zone.includes("/")) {
    throw new Error(
      "Use an IANA time zone, such as America/New_York, or UTC."
    );
  }

  try {
    new Intl.DateTimeFormat("en-US", { timeZone: zone }).format(instant);
  } catch (error) {
    throw new Error("Unrecognized IANA time zone: " + zone);
  }

  return Utilities.formatDate(instant, zone, "yyyy-MM-dd HH:mm:ss");
}

The checks require single cells containing explicit UTC timestamp text with valid calendar components and a recognized zone. After parsing the timestamp, the function compares its UTC components with those supplied in the text. An impossible date therefore produces an error instead of silently becoming a different date.

The last call, to Utilities.formatDate, supplies the instant, destination zone, and output pattern. Its result is a string. Treat column D as a readable display, not as a column for date calculations.

Does UTC account for daylight savings?

UTC stays unchanged during daylight saving transitions; the destination zone and the timestamp’s date determine the local offset.

The sample rows should produce the local text below with the formatting call above. Google’s formatDate documentation describes the explicit time-zone argument used for this conversion.

Row Time zone Expected local text
2 America/New_York 2025-03-08 10:00:00
3 America/New_York 2025-03-09 11:00:00
4 America/Los_Angeles 2025-03-09 01:30:00
5 America/Los_Angeles 2025-03-09 03:30:00
6 America/Phoenix 2025-07-01 09:00:00
7 America/Los_Angeles 2025-07-01 19:00:00
8 America/New_York 2025-11-02 01:30:00
9 America/New_York 2025-11-02 01:30:00

New York uses 15:00 - 5 hours = 10:00 on March 8. On March 9, the calculation changes to 15:00 - 4 hours = 11:00. A formula that subtracts five hours down the entire column would show the second handoff one hour early.

The Los Angeles rows show why local clock readings are a poor basis for elapsed-time calculations. In UTC, the interval is 10:30 UTC - 09:30 UTC = 1 hour. Subtract the local readings and you get 03:30 - 01:30 = 2 hours. The skipped local hour does not add any elapsed time.

Row 7 also crosses a date boundary. Seven hours before July 2 at 02:00 UTC is July 1 at 19:00. The backward clock change appears in rows 8 and 9: UTC advances by one hour, but both local strings read 01:30:00. Keep the source timestamps beside those results. Replacing them with local text would lose that distinction.

Which time-zone values should a handoff tracker accept?

A handoff tracker should accept location-specific IANA identifiers, not abbreviations or fixed hour adjustments.

The function accepts America/New_York, America/Los_Angeles, and America/Phoenix; standalone EST and PST do not pass validation. For a display that stays in UTC, use the explicit UTC exception. Including a slash is not enough to make an identifier valid. The runtime still has to recognize it in the next check.

Use the identifier for the actual destination rather than a familiar nearby city. The Phoenix sample illustrates the problem with treating US locations alike: being in the same country does not mean they follow the same seasonal time changes.

To change a row’s local result, edit its zone. The spreadsheet’s time-zone setting does not control this output. Column C supplies the destination directly to the formatDate time-zone argument.

I prefer a full identifier over a column labeled “local.” Someone outside that location needs the zone to make sense of a handoff copied into a message. Keep that distinction in other remote operations procedures dealing with regional working hours, too.

How do you fix LOCAL_TIME input errors without hiding handoffs?

Fix the source timestamp, zone identifier, or formula arguments rather than turning a handoff error into a blank cell.

The timestamp is a date value instead of text

Date cells reach custom functions as Date objects, according to Google’s custom-function data-type guidance. Rejecting those objects is intentional here.

Set B to plain text, then paste the original UTC strings again. Do not fix a local clock reading by appending Z. That may satisfy the format check, but it labels the reading as UTC and points to the wrong instant.

The zone or timestamp is malformed

Change America/NewYork to America/New_York. The code trims whitespace around the identifier; it cannot supply missing punctuation within it.

Before changing 2025-03-09 15:00:00 to 2025-03-09T15:00:00Z, check that the source really represents UTC. If the source says 2025-02-30T15:00:00Z, request the correct date rather than guessing which March date was intended.

The formula passes a range

Enter =LOCAL_TIME(B2,C2) and fill down. Do not substitute =LOCAL_TIME(B2:B9,C2:C9): Google explains that custom functions receive ranges as arrays, and this implementation rejects arrays.

I would leave IFERROR(...,"") out of this formula. Blank inputs already return a blank result. Hiding the remaining errors would make an invalid timestamp look the same as a handoff that someone has not finished entering.

Can LOCAL_TIME results calculate elapsed time or sort handoffs?

Calculate elapsed time and sort handoffs across zones using the original UTC instants, not the local display strings.

The output from Utilities.formatDate is text. Keep numeric UTC values in a separate calculation column for timestamp subtraction, while retaining the source text for reference.

In the repeated New York hour, 06:30 UTC - 05:30 UTC = 60 minutes. The displayed clock readings would instead suggest zero elapsed time. Use the local text to read deadlines and the UTC values to measure the interval between them.

Before sorting source strings, normalize their UTC notation and fractional-second precision. If you have not normalized them, sort numeric UTC values instead. When you sync task deadlines to a calendar, carry the UTC source with them. Column D cannot supply an unambiguous timestamp.

When should you avoid a display-only handoff function?

Use a different approach when you need recurrence rules, batch processing, or exports with unambiguous timestamps.

LOCAL_TIME converts an existing instant. It does not calculate the UTC instant for each future occurrence of “9:00 a.m. in New York.” For larger sheets, Google’s custom-function guidance recommends reducing separate server calls instead of making many individual function calls.

Requirement Better approach
Recurring “9:00 a.m. New York time” meetings Google Calendar with the event’s named time zone; this function only converts existing UTC instants.
Large imports Batch Apps Script that reads once, formats timestamps, and writes results together to reduce individual calls.
Fixed published reports Scheduled batch processing instead of live formulas.
Unambiguous downstream timestamps Export original UTC plus a separate IANA-zone field; local text cannot distinguish repeated fall-hour occurrences.

Export UTC and the destination identifier in separate fields. Local display text can accompany them for readers, but keep UTC in the export so the two November handoffs remain distinguishable.

Sources

Esc