How to Change the Date Format in Google Sheets

By Gerard Fernandez · Updated · 3 min read

The same date can be shown as 3/25/2026, 25/03/2026, 2026-03-25 or "Wednesday, March 25, 2026". Changing the format only changes how it looks; the date underneath stays the same, so formulas keep working.

Use a built-in date format

  1. Select the cells with dates.
  2. Go to Format → Number.
  3. Choose Date for a short date, or Date time to include the time.

Create a custom date format

  1. Select the dates and go to Format → Number → Custom date and time.
  2. Pick one of the suggested formats, or build your own: click the parts in the box (day, month, year) and choose how each one is shown, for example the month as "Mar" or "March".
  3. Click Apply.
Google Sheets Format menu with the Number submenu open, showing Date, Time, Date time, Duration and Custom date and time
Real test: Format → Number lists the built-in date formats and, near the bottom, Custom date and time.
Custom date and time formats dialog in Google Sheets with Month, Day and Year parts, an Apply button and a list of suggested formats
Real test: the Custom date and time formats dialog. Edit the Month, Day and Year parts or pick a suggestion.

In our test we picked the suggestion August 5, 1930 (the dialog's sample date) and clicked Apply. The cell showed March 25, 2026, while the formula bar still said 3/25/2026: only the look changed.

Dates shown as March 25, 2026, April 2, 2026 and April 15, 2026, with the formula bar still showing 3/25/2026
Real test: the same dates with the new custom format.

Common format codes

These codes work in custom formats and in the TEXT function:

CodeShowsExample
d / ddDay, with or without leading zero5 / 05
ddd / ddddShort or full weekdayWed / Wednesday
m / mmMonth number3 / 03
mmm / mmmmShort or full month nameMar / March
yy / yyyyYear26 / 2026

Format a date inside a formula: TEXT

When you join a date with text, it turns into a number like 46106. Wrap it in TEXT to control how it appears:

="Due on "&TEXT(B2, "d mmm yyyy")

This gives "Due on 25 Mar 2026". In our test, the same formula without TEXT, ="Due on "&A2, gave "Due on 46106". Note that TEXT returns text, so use it for display, not for calculations.

We also checked the codes in the table above on March 25, 2026: ddd dddd gave "Wed Wednesday" and mmm yyyy gave "Mar 2026".

Dates showing in the wrong order (day/month)?

The default order depends on the spreadsheet's locale. Go to File → Settings and set Locale to your country, for example United States for month/day/year or United Kingdom for day/month/year. Existing dates are reformatted automatically.

Watch out: changing the locale doesn't fix dates that were typed as text. If a "date" sits on the left of the cell, Sheets doesn't recognize it. Retype it, or convert it with =DATEVALUE(A2).

Frequently asked questions

Why does my date show as a number like 46106?

The cell is formatted as a number. Apply Format → Number → Date and it displays as a date again. Dates are stored as the number of days since December 30, 1899.

How do I show only the month and year?

Use a custom format such as mmmm yyyy (March 2026), or =TEXT(A2, "mmm yyyy").

How do I calculate with dates?

See how to calculate days between dates.