Convert UTC Handoff Times to Local Time in Google Sheets

How should UTC handoff timestamps be stored in Google Sheets?

Store each handoff’s explicit UTC timestamp as plain text beside its destination IANA time zone in Google Sheets.

Create a sheet named Handoffs with these fictional project labels. Format column B as Plain text before pasting; the trailing Z identifies 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

LOCAL_TIME requires seconds and a Z or +00:00 suffix. Optional fractional seconds accept one to three digits: 2025-03-09T15:00:00.250Z is valid, but output shows whole seconds. Missing UTC markers and nonzero offsets are deliberately rejected.

How do you install the LOCAL_TIME custom function?

Install LOCAL_TIME through Google Sheets’ Apps Script editor, then call =LOCAL_TIME(B2,C2) for each handoff.

1. Select Extensions > Apps Script.
2. Open Code.gs; replace starter myFunction code without deleting unrelated scripts.
3. Paste the complete code below and save. The script requires the Apps Script V8 runtime.
4. Return to Sheets and prepare columns A through C as shown, formatting B as plain text before pasting.
5. Enter Local handoff time in D1 and =LOCAL_TIME(B2,C2) in D2.
6. Fill D2 down through D9. Keep column C visible to identify each result’s zone.

Do not click Run in the editor to calculate cells: editor execution supplies no arguments.

/**
 * 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");
}

Component checks reject impossible dates that JavaScript might otherwise normalize. Zone validation catches misspellings before formatting. No credentials or external service are needed; any future integration secrets belong in Script Properties, not cells or source code.

What local times should the sample handoffs produce?

The sample UTC timestamps produce the following local text when =LOCAL_TIME(B2,C2) is filled through D9.

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

• Rows 2-3: New York’s daylight saving transition changes the offset; fixed subtraction would fail.
• Rows 4-5: Los Angeles skips from 01:30 to 03:30 across one elapsed UTC hour.
• Row 7: July 2 UTC becomes July 1 locally, making the date essential.
• Rows 8-9: Two UTC instants share the repeated fall hour’s 01:30:00. Preserve column B to distinguish them.

Why does LOCAL_TIME convert UTC timestamps correctly for each destination zone?

LOCAL_TIME preserves the UTC instant while formatting it with the destination zone’s date-specific offset, rather than applying a fixed hour adjustment.

First, Array.isArray establishes the single-cell input contract, and empty-value checks return blank for unfinished rows. Type checks keep the inputs textual rather than relying on spreadsheet date coercion. Next, trim removes surrounding whitespace, followed by another blank check. The regular expression defines the accepted UTC notation and captures its date and time components, making parsing explicit rather than permissive.

new Date(text) constructs the instant. getTime and the UTC component getters then verify that parsing preserved the supplied calendar fields, rather than relying on parsing alone. The zone-shape check allows UTC or a slash-containing identifier; Intl.DateTimeFormat then checks whether the runtime recognizes that zone.

Finally, Utilities.formatDate renders the instant using the row’s zone and yyyy-MM-dd HH:mm:ss pattern, rather than the spreadsheet’s time-zone setting. This produces whole-second display text with both date and time. New York’s sample March handoffs therefore display 10:00:00 and 11:00:00 as the applicable offset changes.

Which time-zone values should a handoff tracker accept?

A handoff tracker should accept location-specific IANA identifiers, such as America/New_York, rather than fixed offsets or abbreviations.

Common choices include America/Chicago, America/Denver, America/Los_Angeles, America/Anchorage, and Pacific/Honolulu. Choose the actual location: America/Phoenix stays on the same offset year-round, while Denver changes seasonally.

LOCAL_TIME rejects standalone EST and PST; UTC is an explicit exception for unchanged UTC display.

In Data > Data validation, create a column C dropdown containing approved IANA IDs and reject invalid entries. Store IDs, not labels such as Eastern.

The spreadsheet’s time-zone setting does not select a row’s destination. LOCAL_TIME parses explicit UTC and formats using column C.

How do you fix LOCAL_TIME input errors without hiding handoffs?

Fix LOCAL_TIME errors by correcting the source timestamp, destination zone, or single-cell arguments rather than suppressing error messages.

A date cell produces a plain-text error

Symptom: #ERROR! says the UTC timestamp must be plain text.

Exact fix: Select B, choose Format > Number > Plain text, and re-paste original UTC strings including Z. Formatting an existing date cell cannot reconstruct its original ISO timestamp.

Never append Z to a local clock reading merely to pass validation; that changes its meaning.

A zone abbreviation or typo produces an error

Symptom: EST triggers the IANA-zone message; America/NewYork triggers Unrecognized IANA time zone.

Exact fix: Use America/New_York for New York or America/Los_Angeles for Los Angeles, not PST. Leading and trailing whitespace is trimmed; missing internal punctuation is not repaired.

A date-like timestamp is rejected

Symptom: 2025-03-09 15:00:00 produces the UTC-format error.

Exact fix: Replace it with 2025-03-09T15:00:00Z only if the source represents UTC. Otherwise, resolve the source zone first.

For 2025-02-30T15:00:00Z, correct the impossible date from the source record; the function will not roll it into March.

A range produces a single-cell error

Symptom: =LOCAL_TIME(B2:B9,C2:C9) reports that single cells are required.

Exact fix: Enter =LOCAL_TIME(B2,C2) in D2 and fill down; this implementation does not accept arrays.

Avoid blanket IFERROR(...,"") wrappers that conceal broken handoffs. Empty inputs already return blank.

Can LOCAL_TIME output support duration calculations and chronological sorting?

LOCAL_TIME output is display text, so calculate durations and order mixed-zone handoffs using their original UTC instants instead.

Changing column D’s number format cannot make the text timezone-aware. Keep numeric UTC values in a separate calculation column for elapsed-time arithmetic.

Local-text sorting works only within a shared local-time context, with ambiguity during the repeated fall hour. For source-string sorting, normalize UTC notation and precision first; otherwise, sort numeric UTC values.

When should you avoid a display-only handoff function?

Avoid LOCAL_TIME when the task requires local recurrence scheduling, large batch processing, or an unambiguous timestamp export.

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.