Google Sheets Date Format: How to Change and Fix It

To change a date’s appearance in Google Sheets, select the cell or range and choose Format > Number > Date. For a different style, use Format > Number > Custom date and time; this changes how a date is displayed, not the date value itself.

Change a date format in Google Sheets

In a browser, select the date cells you want to format, then open Format > Number and choose a preset such as Date or Date time. The displayed result depends on the spreadsheet’s locale and the selected format. For example, a date might appear as 11/3/2026, 3 Nov 2026, or November 3, 2026.

Select one cell, a range, or a whole column by clicking its column letter.
Choose Format > Number > Date to apply the standard date style, or select another suitable preset.
Check a few cells to confirm the dates look the way you expect. If not, try a custom format or check the spreadsheet locale.

Formatting a whole column is useful when new dates will be added later: the column’s number format applies to new entries in the formatted range. A format can also be applied to blank cells before dates are entered. If the menu choice appears to have no effect, the cell may contain text rather than a recognized date; formatting cannot turn text into a date by itself.

Choose a date style that fits the sheet

A useful format makes the date easy to read without creating ambiguity. A compact numeric style saves space, while a month name can make reports and shared schedules clearer. The year matters when readers might otherwise confuse dates from different years.

11/3/2026: compact, but readers may interpret the order differently across regions.
2026-11-03: year-first and unambiguous for many readers; useful in records and exports.
3 Nov 2026: readable in a narrow column and less likely to be mistaken for a different month and day.
Tuesday, November 3, 2026: highly descriptive, but takes more space and includes a weekday that can become stale if the date is later edited as text.

Pick a style based on who will read the sheet and what they need to do with the data. If the dates will be sorted, filtered, or used in calculations, keep them as actual date values rather than converting them into text just to achieve a particular appearance.

Use a custom date format

When the built-in choices do not fit, select the cells and open Format > Number > Custom date and time. The dialog lets you assemble a display from date components and choose how each component appears. Depending on the interface, you can add, remove, or reorder the parts, then apply the format.

Common patterns include d for a day without a leading zero, dd for a two-digit day, mmm for an abbreviated month name, mmmm for the full month name, yy for a two-digit year, and yyyy for a four-digit year. For example, a pattern equivalent to dd mmm yyyy displays November 3, 2026 as 03 Nov 2026. A pattern equivalent to mmmm d, yyyy displays it as November 3, 2026.

Custom formats affect presentation, not the underlying date. You can usually change the same cells to another date style later without re-entering their values. Be cautious with two-digit years in long-lived records: they take less space, but make the century less explicit to readers and can be less suitable for data exchange.

Set the spreadsheet locale

The locale helps determine how Sheets interprets dates and which default formats it uses. A numeric entry such as 4/5/2026 can mean April 5 in one regional convention and 4 May in another. If collaborators enter dates using different conventions, a value can be misunderstood before anyone formats the cell.

To review the setting, open File > Settings and check Locale. The locale applies to the spreadsheet, so changing it can affect default date, number, and currency conventions for everyone using that file. It does not necessarily rewrite every custom format already applied, so inspect important columns after making a change. The same settings area also includes the spreadsheet’s time zone, which matters for date-and-time values and time-based calculations.

For a shared sheet, agree on a clear input convention. A month name or an unambiguous year-first entry can reduce confusion, but the safest approach is to check how the value is interpreted in the spreadsheet’s locale. When importing data, inspect a sample of the resulting dates rather than assuming that a source file’s regional convention will be understood automatically.

How Sheets stores dates

A displayed date is usually a numeric value with a date number format. In Google Sheets, date values are represented by serial numbers based on days counted from December 30, 1899; time can be represented as a fraction of a day. For example, changing a date cell to a plain number can reveal a number instead of a familiar calendar date. That does not necessarily mean the date has been lost: it may simply be displayed without a date format.

This distinction explains why changing a date format is different from changing the value. If a genuine date is displayed as 2026-11-03, selecting a month-name format changes its appearance while leaving the underlying date usable for sorting and date arithmetic. If the cell instead contains the characters “2026-11-03” as text, applying a date format alone will not make it behave like a date.

Dates and date-times are also different in practice. A date-only value identifies a calendar day; a date-time value includes a time as well. When a sheet contains timestamps, the spreadsheet time zone can matter, especially for events near midnight. Two users viewing date-times with different regional expectations should confirm the file’s time-zone setting before comparing results.

Use dates in formulas without losing the date value

Once Sheets recognizes an entry as a date, it can be sorted chronologically and used in calculations. The date’s display format does not determine its chronological order: a properly stored November date sorts after an October date even if the cells use different visual styles. Inconsistent text entries, by contrast, can sort alphabetically and produce results that look wrong.

For a date assembled from separate year, month, and day numbers, the DATE function creates a date value. For example, with no special input layout required, entering =DATE(2026,11,3) in a cell returns the date November 3, 2026; the cell’s number format controls how that result appears. This is useful when you want to avoid ambiguity in a manually typed numeric date.

The TEXT function can produce a formatted date string for a label or message, but its result is text, not a date value. With an actual date in A2 and the formula in B2, =TEXT(A2,"dddd, mmmm d, yyyy") returns Tuesday, November 3, 2026 when A2 contains November 3, 2026. Use this for presentation when appropriate, not as a replacement for the original date column if you still need date sorting or calculations.

Fix dates that will not format or sort correctly

If choosing a date style has no visible effect, or sorting puts dates in an unexpected order, check whether the cells contain actual dates or text. Text may result from copying data from a website, importing a file, or entering a value in a form the spreadsheet’s locale does not recognize. A cell’s alignment or appearance can offer clues, but it is not a reliable test on its own.

Check the original entry: compare it with the spreadsheet locale and confirm the intended month and day.
Try a different date format: if a recognized date changes appearance, the stored value is likely a date. If nothing changes, text is one possibility.
Re-enter an unambiguous date: use a clear year-first or month-name convention, then confirm Sheets interprets it as intended.
Check the source and import: imported text may need to be converted or cleaned before date formatting and calculations will work.
Review time zone for timestamps: if a date-time appears to fall on the wrong calendar day, inspect the spreadsheet’s time-zone setting and the source timestamp.

Do not assume that changing the locale will repair every existing text value. The setting can guide interpretation and default formatting, but text that has already been stored as characters may still need conversion or re-entry. Keep an unchanged copy of important imported data until you have verified that corrected dates sort and calculate as expected.

Google Sheets and Excel date formats

The basic idea is similar in both programs: a date can be stored as a value and shown with a number format. The menus are different, however. In Google Sheets in a browser, use Format > Number; in Excel for Microsoft 365 on Windows, date formats are available from the number-format controls on the Home tab. Custom-format interfaces and locale behavior also differ, so an instruction written for Excel may not match the Sheets menus or produce the same regional interpretation.

If a workbook is opened or exported between spreadsheet programs, check important date columns afterward. The underlying values may transfer, but display choices, locale assumptions, and text conversions can affect what users see. For a shared or published file, an explicit format and clear date convention help prevent readers from mistaking a display difference for a changed date.

Frequently Asked Questions

How do I change the date format for an entire column?

Click the column letter to select the column, then choose Format > Number and pick a date style. Check the result in cells that already contain dates; text entries will not become dates just because the column is formatted.

Why does Google Sheets change my date to a number?

Dates are stored as numeric values, so a cell set to a number format can show the serial value instead of a calendar date. Select the cell and choose Format > Number > Date to display it as a date again.

Why is my date being read as the wrong month and day?

The entry may be ambiguous under the spreadsheet’s locale. Check File > Settings, confirm the locale, and use a clear input convention so collaborators do not interpret the same numbers differently.

Can I format a date without changing its value?

Yes. Applying a date preset or custom date format changes how a recognized date is displayed, not the underlying value. A formula such as TEXT, however, returns text and is not interchangeable with the original date for date calculations.

How do I display the month name instead of a number?

Select the date cells and open Format > Number > Custom date and time. Choose or assemble a format that uses an abbreviated month name, such as mmm, or the full month name, such as mmmm.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.