All guides
Data quality

Why Google Sheets timestamps show the wrong time

Hours off, or right but will not sort? The spreadsheet time zone, the Apps Script time zone or UTC text is the cause. How to fix each, and the old rows.

By the QR to Sheets team 8 min readUpdated

Short answer

Google Sheets timestamps usually show the wrong time for one of three reasons: the spreadsheet's own time zone setting, a Google Apps Script running in a different time zone, or a tool that wrote a UTC timestamp as text.

Check File, Settings, Time zone first, because it applies to the whole spreadsheet for every collaborator, then check the script time zone of any Apps Script that writes rows.

QR to Sheets writes each scan as YYYY-MM-DD HH:MM:SS in the workspace timezone; Google Sheets stores it as text that sorts in time order, and VALUE() turns it into a date for formulas.

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.

Written by QR to Sheets, which writes timestamps into customers' sheets and had to fix this in its own product after a customer reported it. The Google Sheets and Apps Script fixes below apply whatever tool writes your rows. Google facts were read from Google's own help and developer pages on 2026-10-01.

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. For a barcode scanner typing into a sheet, an onEdit script that writes the time records it once.
  • 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 is in UTC rather than local 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)).
  • Text without the T and the Z, such as 2026-08-21 20:36:17, needs only =VALUE(A2). That is the form QR to Sheets writes, already in local time.
  • 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 in the timezone set for the workspace. Google Sheets stores that value as text: it reads correctly and sorts in time order, and =VALUE(A2) turns it into a real date-time, so wrap it in VALUE() before date arithmetic or date criteria in MINIFS, MAXIFS or COUNTIFS. 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 by our server rather than by the phone's clock, so a phone with the wrong clock cannot shift them, and the scanner warns when a phone's clock is badly out. A scan that waited on the phone during an outage is written with the time it was scanned, not the time it synced, as long as it is sent within a week, and 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.

Google Forms / Apps Script (DIY) compared with QR to Sheets
CapabilityGoogle Forms / Apps Script (DIY)QR to Sheets
CostFreeFree for 300 scans, then 7 USD per phone per month
Which zone the row usesThe spreadsheet or script time zone, whichever is in playThe workspace timezone, set once for every link
Stored as a real dateYes for Forms and for a Date object; text if a script writes an ISO stringNo: YYYY-MM-DD HH:MM:SS text that sorts in time order; =VALUE(A2) converts it
Phone with a wrong clockForms: not affected. Scripts: depends where the time is takenNot affected; the scanner also warns
Daylight savingHandled if the zone is a named placeHandled; the workspace zone is a named place
Changing the zone laterApplies to the whole spreadsheet; test a few existing rows afterwardsApplies to new rows only

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 turn it into a date. Convert it with a formula instead: =VALUE(A2) for text in the form YYYY-MM-DD HH:MM:SS, more for an ISO string with a T and a Z.

  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

Why are my Google Forms timestamps a few hours off?+

Most often the response spreadsheet's time zone is set to somewhere else. Open the sheet, go to File, Settings, and check Time zone in the General section. Google's help does not spell out the Forms detail, so submit a test response afterwards and compare it with a clock to confirm the fix.

Why will my timestamp column not sort properly?+

It is probably stored as text. Check with =ISTEXT(A2). ISO timestamps ending in Z are the usual culprit. Convert them into a new column with =DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8)), and fix whatever wrote them so new rows arrive as real dates.

Does changing the spreadsheet time zone fix old rows?+

Do not count on it. Google's help says the setting changes the spreadsheet's default date and number formatting; it does not say it rewrites values already in cells, and a timestamp stored as text is never touched. Check a few old rows after changing it and convert anything still wrong with a formula in a new column.

What time zone does Google Apps Script use?+

Its own script time zone, set in the project's settings and stored in the manifest's timeZone field. Formatting a date without naming a zone uses it, so pass the spreadsheet's zone to Utilities.formatDate, or write a Date object and let Sheets handle it.

How do I set the timezone in QR to Sheets?+

In the admin settings, under the Sheet timestamp timezone. New workspaces adopt the admin's browser timezone when the first scanner link is created, if no timestamp has been written yet. The setting applies to new rows only, so set it before the first real shift.

Does a scan made with no signal keep the time it was scanned?+

In QR to Sheets, yes. A scan that waited on the phone during a signal outage is written with the time it was scanned rather than the time it synced, as long as it is sent within a week, and a phone with the wrong clock cannot shift it. The phone's CSV backup also keeps the moment each scan was saved on the device.

Try it on your own Google Sheet

Connect Google, create a scanner link, and open it on the phone that will scan. Have that phone at hand: setup takes a few minutes and ends with your first row in the Sheet. Free covers 300 scans on one phone, and the people you scan never install anything.

Start for free

No credit card required

A second door, booth or classroom is a second phone

Free covers one phone and one scanner link. Premium lets several phones scan at the same time, one per door, booth or classroom, into the same Sheet: $7 per phone per month. Running several classes, events or lists from one phone? Premium on that one phone is $7 a month, with a separate scanner link for each. Paid yearly, it is $70 per phone per year (2 months free).

How many phones will scan at the same time?

$7 x 2 = $14 a month

Sign in with Google and, on the free plan, the billing page opens with this choice. Nothing is charged until you confirm the card checkout, and you can change the number of phones later.

Keep reading