Google Sheets Date Formatting: A Practical Guide to Dates

To format a date in Google Sheets, select the cell or range, then choose Format > Number > Date. For a specific display, use Format > Number > Custom date and time; this changes how a real date appears, not the date value stored in the cell.

What date formatting changes

Google Sheets stores dates as numeric values and displays them according to a number format. A date such as March 8, 2025 can appear as 3/8/2025, 08-Mar-2025, or Saturday, March 8, 2025 without changing the underlying date. That distinction matters: calculations, sorting, and date-based filters rely on the cell value, while the number format controls what readers see.

Sheets represents dates as serial numbers: whole-number steps correspond to days, and a time is represented as a fraction of a day. For example, a date-time value includes both the day and a portion for the time. You usually do not need to see or edit those serial values, but the model explains why formatting a number as a date can suddenly make it look like a calendar date. It also explains why changing a format does not repair an incorrect value or turn ordinary text into a usable date.

Formatting is useful for readability and consistency. A compact format may suit a narrow report column, while a full month name can reduce confusion in a document shared across regions. Choose a format that communicates the needed detail without implying precision the data does not contain.

Apply a built-in date format

In Google Sheets in a web browser, select one or more cells and use Format > Number > Date. The selected values receive the spreadsheet’s standard date display. To show both a date and a time, choose an appropriate date-time option from the Number menu, if available for the selected data, or create a custom format.

Select the cell, column, or range. Selecting a whole column applies the format to current and future entries in that column.
Open Format > Number and choose Date, or choose another suitable preset.
Check the result in the cells. If the format is too detailed, too short, or uses an unsuitable order, open Format > Number > Custom date and time.
For consistent presentation, apply the format to the entire relevant range rather than fixing individual cells one at a time.

You can also insert today’s date as a fixed value with the keyboard shortcut Ctrl+; on Windows or ChromeOS, and ⌘+; on Mac. This inserts the date at the time you press the shortcut; it does not update tomorrow. To show a date that updates automatically, use a function such as TODAY instead. Keyboard shortcuts can depend on the browser and operating system, so check Sheets’ keyboard-shortcut help if a shortcut conflicts with another command.

Choose a format that suits the data

The right date style depends on the audience, the space available, and whether the day of the week or time is important. Common examples include:

3/8/2025 for a compact numeric date. This order can be ambiguous internationally.
08-Mar-2025 for a concise date with the month written as a word.
March 8, 2025 for a readable report or presentation.
Saturday, March 8, 2025 when the weekday is useful, such as for a schedule.
2025-03-08 for an unambiguous year-month-day style often used in data exports and technical records.
A date plus time when the time of an appointment or event is meaningful.

Numeric month-first and day-first patterns are easy to misread: 04/05/2025 could mean April 5 or May 4, depending on convention. If a sheet will be shared internationally, a format with a written month, such as 5 Apr 2025, avoids that particular ambiguity. A year-first format is also clear when a compact numeric style is preferred. Display format alone does not settle how an incoming text value is interpreted; locale settings and the value’s underlying type matter too.

Create a custom date or time format

For a format not offered as a convenient preset, select the cells and choose Format > Number > Custom date and time. In the dialog, choose or arrange date and time components, adjust separators and spacing, and apply the pattern. The available choices let you control whether month and weekday names are abbreviated, whether numbers have leading zeroes, and which components appear.

Common pattern components include d for the day, M for the month, and y for the year. Repeating a letter generally changes the detail: dd can show a leading zero, MMM can show an abbreviated month name, and MMMM can show the full month name. For example, a pattern equivalent to ddd, mmm d, yyyy can display a short weekday and month with a four-digit year. Use the dialog’s preview to confirm the result before applying it, since punctuation and component order affect readability.

Time patterns can include hours, minutes, and seconds. In date-time patterns, uppercase M refers to months, while lowercase m is used for minutes in a time context. The surrounding components determine that context, so inspect the preview when a pattern combines a month and a time. If you only need the date, leave time components out rather than displaying midnight, which can make a date-only value appear to contain meaningful time information.

Locale controls date interpretation

A spreadsheet’s locale influences default date formats and how Sheets interprets many entered date strings. To review it, open File > Settings and check the Locale setting. A locale that commonly uses day-month-year ordering may interpret an ambiguous numeric entry differently from one that commonly uses month-day-year ordering. Choose the locale that fits the spreadsheet’s intended conventions, particularly before importing or entering a large batch of dates.

Changing a cell’s display format and changing the spreadsheet locale are not the same operation. A format changes how selected cells appear. A locale affects the spreadsheet’s regional defaults and can change how text is interpreted when entered or imported. Changing the locale does not guarantee that existing text dates will be converted correctly, so review important data after changing settings. For a shared workbook, document the expected date convention or use unambiguous entries with a written month or year-first order.

Locale settings can also affect other regional conventions, such as number and currency display. Consider that wider impact before changing a spreadsheet’s locale just to make one date column look different. Use a custom format for a presentation-only change; use the locale setting when the spreadsheet itself should follow different regional conventions.

Real dates versus text that looks like a date

A common problem is a cell that displays something date-like but is stored as text. Applying a date format to text usually does not convert it: the characters may remain unchanged, and the entry may not sort chronologically or work in date calculations. This often happens after copying data from a website, importing a CSV file, or receiving dates written in a format that does not match the spreadsheet locale.

Check a suspicious cell by changing its format or testing it in an unused cell. With a value in A2, =ISNUMBER(A2) returns TRUE when Sheets recognizes the value as a number, including a date serial; it returns FALSE for ordinary text. This is a useful clue, not a complete diagnosis, because other numeric values are also numbers. Compare the result with what the cell is supposed to represent.

For a text date that Sheets can parse under the spreadsheet’s conventions, DATEVALUE may convert it to a date value. With the text March 8, 2025 in A2 and a locale that recognizes that wording, =DATEVALUE(A2) returns a date serial corresponding to March 8, 2025; format the result as a date to display it clearly. DATEVALUE can fail or interpret ambiguous text unexpectedly if the entry and locale do not match. For mixed or inconsistent imports, clean and verify the source values rather than assuming one conversion formula will handle every row.

Use dates in formulas, sorting, and schedules

Once cells contain real dates, Sheets can compare them, sort them in chronological order, and use them in calculations. The DATE function creates a date value from year, month, and day components. With no other inputs needed, =DATE(2025,3,8) returns the date March 8, 2025; the cell’s format determines how that date is displayed. DATE is helpful when separate columns hold year, month, and day, or when you want a clear date value rather than relying on ambiguous typed text.

Dates can be used as boundaries in filters or formulas. For example, a date stored as a real value can be compared with another date to identify entries before or after a deadline. When you sort a column, make sure the entries are date values rather than a mixture of values and text; mixed types can produce results that seem out of order. If a date column is used for a schedule, keep the date and any separate descriptive text in distinct columns so the date remains available for sorting and calculations.

The TEXT function is useful when a formula needs to return a formatted date as text, for example for a label. With the actual date March 8, 2025 in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns Saturday, March 8, 2025. Unlike applying a number format to A2, TEXT returns text, not a date value. That result is suitable for a heading or message, but generally not for a column that must keep sorting and calculating as dates.

Time zones and date-time values

A date-time value can change its displayed calendar day when time-zone settings differ, especially when the time is close to midnight. Review the spreadsheet’s time zone in File > Settings if timestamps appear shifted. This setting is particularly relevant to shared schedules, form responses, and records created by integrations. Formatting a timestamp cannot correct a time-zone mismatch; it only changes the display of the stored value.

Decide whether your sheet represents a local appointment, a date on a calendar, or a precise moment shared across regions. Those uses can require different handling. A date-only deadline should not accidentally gain a time, while a meeting across time zones needs enough context for readers to identify the intended local time. If timestamps arrive from another system, check that system’s time zone and the spreadsheet setting before drawing conclusions from the displayed date.

Common problems and useful distinctions

A number appears instead of a date: select the cell and apply a date format. If the result is still unexpected, inspect the underlying value and source data.
A date format has no visible effect: the entry may be text rather than a date value. Check its type and convert or re-import it appropriately.
Dates sort in the wrong order: the column may contain text dates, inconsistent values, or blank rows. Standardize the data before relying on chronological sorting.
The same entry changes meaning: check the spreadsheet locale and avoid ambiguous numeric input when collaborating across regions.
A date shows a time: choose a date-only format if the time is not meaningful; changing the format will not remove an actual time value from calculations.

Google Sheets and Excel both distinguish a value from its display format, but the menus and some format conventions are not identical. In Sheets, use Format > Number; Excel uses its own number-format controls. Do not assume that a menu path or every custom format token transfers unchanged between the programs. For a workbook that moves between them, check representative dates after conversion.

Frequently Asked Questions

How do I change the date format for an entire column?
Select the column letter, then choose Format > Number > Date or a suitable custom format. New entries in that column will generally use the applied formatting, although pasted content and text values may need separate cleanup.

How do I display a date as YYYY-MM-DD?
Select the cells and open Format > Number > Custom date and time. Choose or arrange a year-month-day pattern with a four-digit year and two-digit month and day, then confirm the preview before applying it.

Why does Google Sheets show a number instead of a date?
The cell may contain a date serial currently formatted as a plain number. Apply a date format. If the number does not correspond to the expected date, inspect the source value rather than changing the display alone.

How can I stop day and month from being swapped?
Set the appropriate spreadsheet locale under File > Settings and use unambiguous input, such as a written month or year-first date. Avoid relying on a short entry like 04/05 when collaborators may use different conventions.

Can I make a cell always show today’s date?
The Ctrl+; shortcut on Windows inserts a fixed date. For a date that updates automatically, use TODAY in a cell; remember that a formula result can recalculate rather than preserve the date on which a record was created.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.