Spreadsheet and API Tools: Google Sheets Date Settings

Spreadsheet and API tools in Google Sheets can quietly change what a date means without changing the value shown in a cell. The file's time zone and locale affect different things: the time zone governs values derived from the current moment, while the locale affects how text dates are interpreted and displayed.

What to take away

The two settings

In Google Sheets, open File > Settings and use the General section to set Time zone and Locale. These spreadsheet-level settings can be different from the settings on the device or browser you use to open the file. Record them near the top of the sheet so readers know how to interpret its dates.

Setting	What it changes	Symptom when wrong

For example, 03/04/2024 means March 4, 2024, in the US en-US locale, where dates commonly use month/day/year order. In the UK en-GB locale, where dates commonly use day/month/year order, it means 3 April, 2024. Dates with a component above twelve cannot be transposed in the same way, so a column can look mostly correct while its ambiguous rows are wrong.

The epoch

Google Sheets represents dates as day serials, with serial zero corresponding to December 30, 1899—not January 1, 1900. That offset lets its serials align with Excel's for dates from March 1900 onward despite Excel's early-1900 compatibility anomaly. A date is a whole number of days; a time adds a fraction of a day, so subtracting two date values gives a day difference and multiplying a time difference by 24 gives hours.

The early-1900 defect is the fictitious date February 29, 1900. Lotus 1-2-3 treated 1900 as a leap year, and Excel retained that behavior for compatibility; 1900 was not a leap year in the Gregorian calendar.

As a result, Excel serial 60 represents the nonexistent February 29, 1900, while the continuous date sequence from the Google Sheets epoch places that serial on February 28. Excel and Sheets serials align again from March 1, 1900, but do not assume early-1900 serials are interchangeable. Test that boundary when moving historical dates between tools.

The practical consequence is simple. Move dates between tools as text in ISO 8601 order rather than as serials. Text carries its own meaning; a serial carries a meaning that lives in the file it came from.

The import boundary

Data arriving from elsewhere may be kept as text or converted to a date when it enters the sheet; the import method and settings determine which. When Sheets parses a text date, it uses the file's locale, which can cause the selective transposition described above. With the Google Sheets API, a write using valueInputOption=USER_ENTERED is parsed as if entered in the interface, while valueInputOption=RAW stores the supplied value without that parsing.

Three habits remove most of the risk. Require ISO 8601 order at the source, such as 2024-03-04, which avoids ambiguous month/day order. Convert once in a dedicated column rather than relying on display formatting, and check that the converted value is numeric instead of judging by appearance: a text date and a real date can look identical.

A holiday range is where this can affect a workday count. A text entry in a range may not fail on import, but a business-day function can fail to exclude it as a holiday. Keep closure dates as actual date values; see how closure calendars are made for the calendar side of that task.

Workday calculations and date settings

Google Sheets provides NETWORKDAYS(start_date, end_date, [holidays]) to count working days between two dates, including the endpoints when they are weekdays and excluding weekends and listed holidays. WORKDAY(start_date, days, [holidays]) moves forward or backward by a number of working days; the start date is not counted as a working-day step.

Both functions depend on valid date values and, when supplied, a valid holiday range. A locale-misparsed date can put a holiday on the wrong day, changing the count or the returned workday. Use actual date values for the start, end, and holiday cells rather than ambiguous text.

Typed dates carry no zone

A date or time typed into a cell is a bare number, not a time-zone-aware moment. Changing the file's time zone does not shift that stored date value; interpreting it as a moment requires a zone that the cell itself does not contain.

For a window definition, that is exactly right: 09:00 means the schedule's nine, and the schedule's zone lives beside it rather than inside it. For an event log, it is not enough, because events logged in two regions can become indistinguishable. In Google Sheets, TODAY() resolves the current date and NOW() the current date and time using the spreadsheet's time zone; DATEVALUE() parses date text according to the locale, while TO_DATE() converts a number to a date. Log event moments with an explicit offset as text, then convert them on the way in.

A time zone is more than a fixed offset: it identifies the local time rules that can change during the year. The distinction matters because a fixed offset stored in place of a zone can be right for part of the year and an hour out for the rest; see time zone for the difference.

Crossing into an interface

The boundary contract has three parts: a plain date is text in ISO 8601 order, a moment is text with an offset, and neither is ever an unexplained bare serial. The counting convention must cross too. NETWORKDAYS includes eligible start and end dates, while WORKDAY treats the start date as the reference point rather than counting it as a workday step; an interface must match the function it is replacing.

For the Google Sheets API, the spreadsheets.values.get endpoint returns dates as formatted strings with valueRenderOption=FORMATTED_VALUE (the default), or as numeric serial values with valueRenderOption=UNFORMATTED_VALUE. With unformatted values, dateTimeRenderOption=SERIAL_NUMBER returns serials, while dateTimeRenderOption=FORMATTED_STRING returns formatted date-time strings. Formatted values can depend on the file's locale. For a stable text value, use a formula such as =TEXT(A1,"yyyy-mm-dd") and read its result as text rather than treating a serial as an API date.

The API's number is a spreadsheet serial, not a self-describing date with an epoch or time zone attached. Test the endpoint and counting conventions before integrating; see spreadsheet and interface tools, counting between two dates, and working hours for related date and time calculations.

Common questions

Where should the file's timezone and locale be recorded?

Set them in Google Sheets under File > Settings > General, where Time zone and Locale appear. Also record both as text near the top of the sheet so readers can understand the date values without opening the settings dialog.

Why do some of my imported dates transpose the day and month?

A locale with month/day/year order can read 03/04/2024 as March 4, while a locale with day/month/year order can read it as 3 April. A component above twelve makes the order unambiguous, which is why the problem can look intermittent.

Should I ever send a serial number to an interface?

Do not send an unexplained serial as though it were a date. In the Google Sheets API's spreadsheets.values.get response, UNFORMATTED_VALUE can return a date as a number, while FORMATTED_VALUE returns its displayed string. For stable text, use a text value such as one produced by =TEXT(A1,"yyyy-mm-dd"); otherwise the receiver needs to know the spreadsheet's date convention and formatting.

Does the file's timezone affect a difference between two typed dates?

No. Two typed dates are bare numbers, so their difference is not shifted by a time-zone change. The time zone affects values resolved from the current moment, such as TODAY() and NOW(), and the locale affects how text dates are parsed by functions such as DATEVALUE().