1. LandOffer
  2. Blog
  3. All articles
  4. Google Sheets Dates and IDs Look Wrong: Separate Stored Values, Formats, and Locale

Productivity & Workflows

Google Sheets Dates and IDs Look Wrong: Separate Stored Values, Formats, and Locale

Use observed synthetic CSV imports to distinguish ID collisions, date interpretation and amount display. Preserve raw text and define field-specific repair acceptance before changing a workbook.

9 min readLandOffer team

When dates or IDs look wrong in Google Sheets, preserve the original file and check three things separately: the stored value, its display format, and the spreadsheet’s locale. Formatting can change how a value appears, but it cannot recover an identifier’s lost source spelling or determine what an ambiguous date was supposed to mean. Confirm the source schema before repairing anything.

For this guide from LandOffer.ai, our production team imported the same synthetic CSV twice in an isolated Google spreadsheet, with conversion enabled and disabled. We inspected the visible grids and typed cell readbacks. Those observations are real tests of fictional data; the later repair decisions are illustrative and were not applied to a business file.

A geologist distinguishes an intact mineral specimen from the appearance produced by a viewing lens and filter.

Diagnose the layer before choosing a fix

Identify the source field’s meaning before choosing its display. An employee number might be an identifier whose exact characters matter. An amount might be a number whose two-decimal display matters. A date might be a numeric date value, plain text, or an ambiguous source string that has already been interpreted.

Google documents number and date formats as controls over appearance. Sheets stores date and time values numerically; its day count starts on December 30, 1899. That means a cell displaying a date can have a numeric underlying value, while an adjacent date-looking cell remains text. Matching appearance does not establish matching types.

Record the spreadsheet locale and time zone during diagnosis. In our isolated test, they were en_US and Etc/GMT. Google’s locale setting affects the spreadsheet as a whole and its default currency, date, and number formatting; it is not simply the interface language preference. Changing it in a shared workbook affects other collaborators too.

Do not switch the whole workbook’s locale as an improvised repair before understanding existing data and dependencies. It does not supply the missing evidence about what a source author intended. Keep the original import available while you decide which fields need text preservation, explicit parsing, or only a display adjustment.

Worked asset: the same CSV under two import choices

This is the exact synthetic sample used in the test. It contains a header and three data rows. The amount containing a thousands separator is quoted so its comma remains inside one CSV field.

record_id,date_text,amount_text
00123,03/04/2026,"1,234.50"
00007,13/04/2026,17.25
123,2026-04-03,0.10

On October 25, 2026, the team used the native Google Sheets import dialog with separator detection and imported the sample into separate tabs of the same isolated file. The control was labeled “Convert text to numbers, dates, and formulas.” We compared an enabled import with a disabled import rather than assuming what that checkbox meant from an older tutorial.

The following table reconciles the actual stored-value readbacks with the source. The stored types reveal differences that the visible date and amount strings alone do not establish.

Source field and row Conversion enabled: stored value and display Conversion disabled: stored value
ID 00123 Number 123; displays 123 Text 00123
ID 00007 Number 7; displays 7 Text 00007
ID 123 Number 123; displays 123 Text 123
Date 03/04/2026 Number 46085 with DATE format mm/dd/yyyy; displays 03/04/2026 Text 03/04/2026
Date 13/04/2026 Text 13/04/2026 Text 13/04/2026
Date 2026-04-03 Number 46115 with DATE format yyyy-mm-dd; displays 2026-04-03 Text 2026-04-03
Amount 1,234.50 Number 1234.5 with format #,##0.00; displays 1,234.50 Text 1,234.50
Amount 17.25 Number 17.25; displays 17.25 Text 17.25
Amount 0.10 Number 0.1; displays 0.1 Text 0.10

With conversion disabled, both user-entered and effective values for all nine data cells were strings matching the parsed source fields, including the leading zeros and 0.10. The CSV’s surrounding quotation marks are field syntax; they are not part of the amount string stored in the cell.

With conversion enabled, the first and third IDs both became the number 123. The source had three distinct ID strings; the imported numeric column had only two distinct values. Two distinct source IDs now share one numeric value in this sample. Do not deduplicate, join, or update business records using that numeric column before reconciling it with the source.

The two slash dates also produced different types. The ambiguous 03/04/2026 became March 4, 2026: serial 46085. The source’s 13/04/2026 remained text in this test. A column that looks like dates can therefore contain both numeric dates and strings. This observation does not establish that the first interpretation was wrong; the synthetic source does not independently specify its intended date convention.

The disabled import is useful as a preservation layer, not automatically a finished analytical table. Its amount cells are strings, so they still need an approved conversion before you rely on numeric calculations. Its dates are also strings, so a date-looking sort order does not establish chronological order. Preserving text prevents one category of interpretation, but the work of defining meaning remains.

For your own authorized comparison, use a small non-sensitive sample with the problematic fields represented. Record the chosen import destination so an experiment does not replace an existing working sheet. In the tested workflow, the results were kept in separate tabs. Compare the parsed row and column counts as well as values; a separator mistake can move an amount into the wrong field before type conversion is even considered.

Keep source order as evidence for this small test, but do not assume row position is a safe business key after sorting or filtering. If the production data has already been rearranged, reconciliation needs a stable reference from the source. Recovering the right-looking ID on the wrong row would still be an incorrect repair.

An ID format cannot reconstruct the original identity

Google’s documented custom-format syntax can display insignificant zeros. A pattern such as 00000 can therefore make the number 123 appear as five digits. We did not apply that format in the test; this is a documented formatting rule, not an observed repair result.

The limitation is visible in the source comparison. Both 00123 and 123 became numeric 123. Applying the same five-digit display to each would not tell you which source spelling belonged to which record. It could also make a legitimate three-character ID look like a five-character ID it never had.

If exact ID characters matter, recover them from an authoritative source using a trustworthy row reference or another stable key. Do not invent leading zeros from a preferred visual width. If the converted column is the only remaining key, identify the ambiguity and seek a source re-export rather than choosing a record by guesswork.

Preserved raw text lets the next analyst trace an approved working value back to its source spelling. A colleague should be able to see both the source identifier and any approved normalized field, together with the rule used to create it. Overwriting the only surviving evidence makes later reconciliation harder even when today’s sample seems obvious.

Worked asset: field-specific repair and acceptance decisions

A craftsperson keeps distinct original geometric blocks separate while comparing their printed impressions.

The following is a fictional business continuation, separate from the actual import test. Analyst Salma asks source owner Victor for a schema. In this story, Victor confirms that IDs are variable-length character strings, slash dates use day/month/year, ISO dates retain their stated order, and amounts use a comma for grouping and a period for decimals. Those confirmations were authored for the example; no real source owner supplied them.

Salma’s completed decision record chooses the conversion-disabled tab as preserved raw evidence. It proposes separate typed working fields under the approved schema rather than editing the raw columns. No date repair, number-format change, locale change, or typed-field creation was executed in Google Sheets for this article.

Field Actual observation to retain Illustrative repair decision Acceptance evidence and current result
IDs Raw strings remain 00123, 00007, 123; enabled import collapsed two to 123 Use the raw character IDs; reject automatic padding Exact three-string preservation already observed in disabled import; downstream join not tested
Slash date row 1 Enabled import stores 46085, March 4; raw remains 03/04/2026 Under the fictional DMY confirmation, parse raw as April 3, 2026 Expected date is 2026-04-03; typed replacement not created or verified
Slash date row 2 Both imports retain text 13/04/2026 Under that same DMY schema, parse as April 13, 2026 Expected date is 2026-04-13; not a completed Sheets conversion
ISO date row 3 Enabled import stores 46115, April 3; raw remains 2026-04-03 Preserve raw and create the approved date field consistently with other rows Source order is explicit; working-field type and downstream date use remain untested
Amounts Enabled values 1234.5, 17.25, 0.1; disabled values retain source spelling Parse under confirmed separators; use two-decimal display if required by the report Expected numeric total 1251.85 verified offline; Sheets calculation and formatting not tested

The completed outcome is a bounded repair decision, not a claim that the business sheet is fixed. The ID preservation gate has actual evidence. The proposed typed dates and amount presentation still need authorized implementation and readback. The ambiguous date becomes an error relative to the fictional DMY schema only after that schema is supplied.

For row 1, changing the display of serial 46085 to a day/month format would still represent March 4. It would not turn it into April 3. The target date has to be derived from the preserved source under the approved parsing rule, not manufactured by rearranging what the existing numeric value looks like.

Likewise, displaying 0.1 with two decimal places can be appropriate for an amount report without implying that the numeric value was corrupted. The source string 0.10 and numeric 0.1 carry different representation information, but they describe the same decimal amount under this example’s confirmed convention. Keep the source spelling if it matters to an audit or exchange format.

Validate meaning and type before replacing the working data

In an authorized repair, check each field against its own acceptance rule. For IDs, compare exact characters and uniqueness. For dates, compare the intended calendar day and numeric-versus-text state. For amounts, compare the parsed value, total, and required display precision. An acceptance record should separately confirm exact ID strings, intended calendar days and parsed amounts; visual inspection alone cannot establish those facts.

Keep the raw import separate from the proposed working table until those checks pass and the responsible data owner accepts any unresolved exceptions. For a small sample, record row-level comparisons; for larger data, establish a reproducible reconciliation method and review exceptions. Do not silently treat an unparsed date as a blank or discard a row because one field did not convert.

This test covered one small CSV, one locale, and one current native import control. It did not test another locale, repaired data, formulas, exports, or a downstream system. The typed readbacks support the nine observations above; they do not promise that every CSV will be parsed identically. Review the controls and resulting types again in your authorized environment.

Before the next import, preserve the source, agree on the field schema, and decide what should remain text. After import, check stored values alongside formats and locale before selecting a repair. More workplace guides are available through LandOffer.ai; this guide does not modify your workbook or certify its business data.

Sources and Further Reading

  • Google: Date and number formats — Appearance, numeric date serials, epoch and number-format syntax. Updated October 20, 2026; checked October 25, 2026.
  • Google: Set a spreadsheet’s location and calculation settings — Spreadsheet-wide locale/time-zone settings and default formats. Undated; checked October 25, 2026.
  • Google: Format numbers in a spreadsheet — Date, currency and custom number formats, including zero placeholders. Undated; checked October 25, 2026.
  • Observed test scope: Native CSV imports with conversion enabled/disabled in an isolated synthetic-only spreadsheet on October 25, 2026; visible-grid screenshots and typed cell readbacks retained locally. No repair or locale-change test was performed.