Google Sheets Date Format: Formulas, Examples, and Tips

To show a date in a particular format with a Google Sheets formula, use TEXT: if A2 contains a date, enter =TEXT(A2,"yyyy-mm-dd") to return a text value such as 2026-11-03. If you want to keep the result as a usable date, format the cell instead: select it and choose Format > Number > Custom date and time.

How date formatting works in Google Sheets

A date in Sheets is a numeric value that the spreadsheet can display in different ways. The underlying value lets Sheets sort dates chronologically, calculate intervals, and use dates in functions. Formatting changes how that value looks; it does not, by itself, change the date. For instance, the same date might appear as 11/3/2026, Nov 3, 2026, or 2026-11-03 depending on its format.

A formula can either return a date value or return text that looks like a date. That distinction is important. A date-producing formula such as DATE can be formatted and used in later date calculations. TEXT converts a value to a string according to a pattern. The string may look like a date, but Sheets may not treat it as one when sorting, subtracting, or using it in another calculation.

Use a custom cell format when the goal is only to change how a date is displayed. Use TEXT when the formatted date needs to appear inside a text result, such as a sentence, label, or exported identifier.

Choose a date pattern for TEXT

The pattern inside TEXT determines which parts of the date appear and how they are written. With a date in A2, =TEXT(A2,"mmm d, yyyy") returns a result such as Nov 3, 2026. The letters in the pattern represent date components, and repeated letters generally control whether a component appears in a short or longer form.

d shows the day without a leading zero; dd adds one, as in 3 or 03.
ddd gives an abbreviated weekday, and dddd gives the full weekday name.
m shows the month number; mm adds a leading zero. mmm and mmmm show abbreviated and full month names.
yy shows a two-digit year, while yyyy shows the four-digit year.

For example, if A2 is November 3, 2026, then with A2 containing that date, =TEXT(A2,"dddd, mmmm d, yyyy") returns Tuesday, November 3, 2026. To make a compact, unambiguous label, with the same date in A2 use =TEXT(A2,"yyyy-mm-dd"), which returns 2026-11-03. A numeric month is often easier to sort or scan consistently than a month name, though the formula result is still text.

Patterns can also include punctuation and spaces. Pick a pattern that suits the audience: a month name can be easier to read in a report, while a year-first numeric form is compact and reduces ambiguity across regions. If you include time tokens as well, be careful with m: in date-and-time patterns it can represent minutes in a time context, rather than the month.

Keep a real date or return formatted text?

Formatting a cell is usually the better choice when the output will be used as a date later. Select the date cell or range, then use Format > Number > Custom date and time. Choose a suggested format or build one in the editor, then apply it. The stored value remains a date, so formulas can still compare it, calculate with it, or sort it in chronological order.

TEXT is useful when the date is part of a larger text result. For example, if A2 holds a valid date, with A2 as the input use ="Due "&TEXT(A2,"mmm d, yyyy") to return text like Due Nov 3, 2026. Since this combined result is text, it is not a date cell that can be reformatted later. If another formula needs the original date, refer to A2 rather than extracting the date from the sentence.

As a practical rule, keep dates as date values in working data, and apply a display format to the cells. Convert them to text at the point where you need a human-readable label, report heading, or text export. This avoids the common problem of making a date look right while quietly losing its date behavior.

Create dates with DATE and date components

Use DATE when a formula needs to construct a date from year, month, and day numbers. With the year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2). If those cells contain 2026, 11, and 3, respectively, the result is November 3, 2026; the exact on-screen appearance depends on the cell’s number format.

DATE is also useful when data arrives in separate columns. It gives Sheets a date value instead of joining the components into a text string. It can normalize out-of-range month or day numbers by carrying them into the next or previous month, so check the source data if unexpected component values might indicate an error.

For a fixed date, with no input cells required, use =DATE(2026,11,3). This returns a date value for November 3, 2026, not a preformatted text string. Apply a custom format if you want a specific display. If you need the current date instead, enter =TODAY(); it returns the current date and updates as the spreadsheet recalculates. Format that cell like any other date.

Convert date-like text and handle locale

Sometimes imported data looks like a date but is stored as text. If A2 contains a date string that Sheets recognizes under the spreadsheet’s locale, with that text in A2 use =DATEVALUE(A2) to convert it to a date value. Format the result as needed. DATEVALUE is not a universal parser for every wording or date convention, so an unrecognized string may produce an error or a result different from what you intended.

Locale matters because a string such as 04/05/2026 can mean April 5 or May 4. Check the spreadsheet’s locale under File > Settings, and make sure the input format matches the convention Sheets is using. When possible, avoid ambiguous dates in source files; use clearly labeled month names or a consistent year-month-day convention. For data with year, month, and day in separate numeric columns, DATE can be more reliable than asking Sheets to interpret a string.

Changing the spreadsheet locale can affect default date, number, and currency conventions across the file. It is not simply a cosmetic fix for one cell. If a shared sheet serves people in different regions, consider displaying dates with an explicit month name or another format everyone can interpret, while keeping the underlying values as dates.

Common date formula problems

The cell shows a number. The value may be a valid date shown with a plain number format. Select the cell and choose Format > Number, then choose a date format or set a custom one.
The date does not change appearance. The input may be text rather than a date value. A display format cannot turn arbitrary text into a date; convert recognized text with DATEVALUE or rebuild it from numeric components with DATE.
The day and month appear reversed. Check the file’s locale and the source convention. Values entered as text can be interpreted differently from values entered with date components.
A date formula displays as text. If the formula uses TEXT, that is expected: TEXT returns a string. Use the date-producing formula or original date cell when you need calculations, and format that cell instead.
The formula gives an error after pasting. Check quotation marks, parentheses, and the spreadsheet’s formula separator. Depending on locale settings, a sheet may require semicolons instead of commas between function arguments.

Also check whether a cell contains a time along with its date. A date-time value can display only the date if the time portion is hidden by the format, but calculations may still include that time. If two values look like the same day yet compare differently, inspect their full values or use a date-only source where appropriate.

Google Sheets and Excel date formats

The basic approach is familiar across Google Sheets and Excel: store dates as date values, use a cell format to change their appearance, and use a text-formatting function when a formula must produce a formatted string. However, menu names and the available custom-format editors differ between the applications, so use Google Sheets’ Format > Number > Custom date and time path for the steps here.

Do not assume that a date-looking value copied from another spreadsheet will be interpreted identically. Regional settings, imported text, and custom display patterns can affect how it appears. If a workbook is shared between applications, check both the underlying values and the visible format after importing or exporting rather than relying on appearance alone.

Frequently Asked Questions

How do I change the date format in Google Sheets without a formula?
Select the date cells and choose Format > Number > Custom date and time. Pick or create a pattern and apply it. This changes the display while preserving date values for calculations.

How do I format a date as YYYY-MM-DD in a formula?
If A2 contains a valid date, use =TEXT(A2,"yyyy-mm-dd"). It returns text in that layout. If you need a date value rather than text, apply a custom date format to A2 or to the date formula’s result.

Why does TEXT return a number or an unexpected date?
Check that the referenced cell contains the date you expect and that the pattern is spelled correctly. Also check the locale if the source was entered as text; an ambiguous date may have been interpreted with a different month-and-day order.

Can I use TEXT to format a date and still calculate with the result?
Not reliably as a date value: TEXT returns text. Keep the original date or a date-producing formula for calculations, and use the TEXT result only where formatted text is required.

How can I display the month name instead of its number?
Use a month-name pattern in TEXT, such as =TEXT(A2,"mmmm d, yyyy") when A2 contains a valid date; it returns a full month name with the day and year. For a cell display rather than text, choose a custom date format with a full month name.

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.