Why Google Sheets timestamps show the wrong time

A wrong timestamp in Google Sheets almost always has a boring cause, but there are several of them and they produce different symptoms. Rows that are exactly eight hours out, rows that look right but will not sort, and rows that were right until the clocks changed are three different problems with three different fixes.

Cause 1: the spreadsheet's time zone

Every Google Sheet has one time zone, set under File, Settings, in the General section. Google's help page on the setting states that changing the locale and time zone changes the spreadsheet's default currency, date and number formatting, that the change applies to the entire spreadsheet, and that everyone working on it sees the change regardless of their location. A sheet created by someone in another country, or copied from a template, often carries the wrong zone for the people using it.

Google Forms response sheets are where people most often notice this. Google's help pages do not spell out how the Forms Timestamp column relates to the setting, so test it rather than trusting a rule: submit one response, compare its Timestamp with a wall clock, and if it is out by whole hours, set the response spreadsheet's time zone and submit another.

• Choose a named zone such as Asia/Manila or America/New_York, not a fixed offset. A named zone follows daylight saving; an offset does not.
• Never use =NOW() as a timestamp. Google's help describes NOW as volatile: it updates on every edit, so it always shows the time of the last recalculation, not the time the row arrived.
• If different sites need different local times, give each site its own spreadsheet, or keep one zone and add a converted column per site.

Cause 2: a Google Apps Script in another time zone

An Apps Script project has its own script time zone, set in Project Settings and stored as the timeZone field of its manifest. Formatting a date inside the script uses that zone unless told otherwise, so a script created in one region and run against a sheet set to another writes times that are out by the difference.

• The safest fix: append a Date object, as in sheet.appendRow([new Date(), value]). Sheets receives a real date-time value rather than text.
• If a string is required, format it with an explicit zone: Utilities.formatDate(date, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyy-MM-dd HH:mm:ss").
• Never write new Date().toISOString(). It produces a UTC string such as 2026-08-21T12:36:17.527Z, which is both in the wrong zone for most readers and stored as text. That is cause 3.

Cause 3: a UTC timestamp written as text

Many tools and integrations write timestamps in ISO 8601 format with a trailing Z, meaning UTC. Google Sheets does not recognise that form as a date, so the cell is stored as text. It looks roughly right, which is why this one survives for months: it sorts alphabetically rather than by time, date filters skip it, and any formula that subtracts one time from another returns an error or nonsense.

• Test it: =ISTEXT(A2) returns TRUE for a timestamp stored as text.
• Convert it to a real UTC date-time: =DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8)).
• Shift it to local time with a fixed offset only if your zone has no daylight saving, for example +8/24 for UTC+8. Where the clocks change, fix the source instead, because a fixed offset will be wrong for half the year.
• Put the converted values on a new column or tab. Leave the original text column alone, so the conversion can be checked and redone.

Cause 4: daylight saving

Rows that were correct until a date in March, April, October or November, and are exactly one hour out after it, have been shifted by a fixed offset somewhere. The spreadsheet zone may be set to a GMT offset rather than a named place, or a formula may add a constant. Replace the offset with a named zone and the hour comes back.

Cause 5: a device with the wrong clock

Any tool that stamps a row with the phone's own time inherits whatever the phone thinks the time is, and phones used as shared scanners are often left with a wrong date or zone. A Google Form stamps a response on Google's side, which avoids this. It is worth asking of any other tool whose clock it uses.

How QR to Sheets writes timestamps

This is the product we make, so read it as vendor material. Each scan is written as YYYY-MM-DD HH:MM:SS, a form Google Sheets parses as a real date and time, in the timezone set for the workspace. A new workspace adopts the admin's browser timezone when its first scanner link is created, as long as it has never written a timestamp; an admin can change it in settings at any time, and a workspace with no timezone set writes UTC.

Rows are stamped with the time our server received the scan, so a phone with the wrong clock cannot shift them, and the scanner warns when a phone's clock is badly out. One consequence to know: a scan that waited on the phone during an outage carries the time it synced, while the phone's own CSV backup keeps the moment it was saved on the device. Changing the workspace timezone affects new rows only; rows already written stay exactly as they were.

Step by step

1. 1

   Work out which symptom you have

   Wrong by a whole number of hours points to a time zone. Right-looking but refusing to sort, filter or calculate points to text. Wrong by exactly one hour since a date in spring or autumn points to daylight saving.

2. 2

   Check the spreadsheet time zone

   File, Settings, General, Time zone. Pick the named zone for the place the rows describe, such as Europe/London or America/Chicago, rather than a fixed GMT offset.

3. 3

   Check any script that writes rows

   An Apps Script project has its own time zone in Project Settings. Write a Date object rather than a formatted string, or format with the spreadsheet's own time zone.

4. 4

   Test for text

   Put =ISTEXT(A2) next to a timestamp. TRUE means Sheets is storing it as text, and no amount of reformatting will make it sort as a date. Convert it with a formula instead.

5. 5

   Fix the source before the old rows

   Correct whatever writes the timestamps first, so the problem stops growing, then convert the historical rows on a separate tab and leave the originals untouched.

Frequently asked questions