Google Sheets lets you change how dates look without changing the actual date

When you type a date into Google Sheets, the program stores the real date — the actual day — separately from how it displays on your screen. You can change what you see without breaking any calculations that depend on that date. A date that looks like "12/25/2024" can become "December 25, 2024" or "25-Dec-24" or any other format you choose, and formulas that add days or count months will still work correctly.

The fastest way is to select the cells with dates, open the Format menu, choose "Number," then pick "Date" or "Time" from the list on the left. Google Sheets shows you format options when ready. If none of those match what you want, you can create a custom format by typing your own pattern — like yyyy-mm-dd for year-month-day order, or dddd, mmmm d, yyyy for the full weekday name, month name, day, and year.

Key Takeaways

  • Select the cells containing dates, then go to Format > Number > Date to see built-in format options.
  • Google Sheets stores the actual date separately from how it displays, so changing the format does not change calculations.
  • Custom number formats use letter codes: y for year, m for month, d for day, h for hour, and repeating letters (yyyy, mmmm) make them longer or spelled out.
  • If a date shows as a number like 45261, the cell is formatted as a number instead of a date — select it and change the format to Date.

Using the built-in date formats

Open the spreadsheet and click on any cell that contains a date. If you have multiple dates to format the same way, click the first one, then hold Shift and click the last one to select the range. You can also click a column header to select the entire column.

Go to the Format menu at the top, then click "Number." A panel opens on the right side showing categories on the left. Click "Date" to see all the date formats Google Sheets offers. The list includes formats like "Jan 1, 2024," "1/1/24," "1/1/2024," and others. Click the one you want, and it applies when ready to every selected cell.

If you want times instead, click "Time" in that same list. You will see options like "1:30:45 PM" or "13:30:45" (24-hour format). You can also choose "Date time" to show both the date and the time together.

Creating a custom date format

If none of the built-in formats match what you need, you can write your own. Select your date cells, go to Format > Number, and at the very bottom of the category list on the left, click "Custom number format." A text box appears where you can type a pattern.

Use these letter codes to build your pattern:

  • d = day of the month as a number (1, 2, 3... 31)
  • dd = day with a leading zero (01, 02, 03... 31)
  • ddd = day of the week abbreviated (Mon, Tue, Wed)
  • dddd = full day of the week name (Monday, Tuesday, Wednesday)
  • m = month as a number (1, 2, 3... 12)
  • mm = month with a leading zero (01, 02, 03... 12)
  • mmm = month abbreviated (Jan, Feb, Mar)
  • mmmm = full month name (January, February, March)
  • yy = year as two digits (24 for 2024)
  • yyyy = year as four digits (2024)

You can combine these with any text or punctuation you want. For example, mmmm d, yyyy produces "January 15, 2024." The format dd/mm/yyyy produces "15/01/2024." The format dddd, mmmm d produces "Monday, January 15." Type your pattern and click "explore."

Fixing dates that show as numbers

Sometimes a date appears as a long number like 45261 instead of a readable date. This happens when Google Sheets is treating the cell as a plain number rather than a date. The number itself is correct — it is just the display format that is wrong.

Click the cell showing the number, go to Format > Number, and click "Date." Choose any date format from the list. The number converts to a readable date when ready. If you want a specific format, you can pick one from the built-in list or create a custom format using the codes above.

This also happens sometimes when you copy dates from another program or website. The data came in as a number, not as a date. Reformatting fixes it without losing any information.

Formatting dates in different time zones or languages

Google Sheets uses your account's language and time zone settings to display dates. If you want dates to show in a different language — like "enero" instead of "January" — you need to change your Google account settings, not the spreadsheet format.

Go to your Google Account settings (myaccount.google.com), click "Personal info" on the left, then scroll to "Language and region." Change your language there, and dates in all your Google Sheets will update to match. This affects the entire account, not just one spreadsheet.

If you are sharing a spreadsheet with people in different time zones, remember that the date itself does not change — only how it displays. A date of January 15, 2024 is the same date everywhere. Times, however, do shift by time zone, so if you are working with times rather than just dates, make sure everyone knows which zone the times are in.

Keeping dates consistent across a shared spreadsheet

When multiple people edit the same spreadsheet, dates can end up in different formats if everyone formats their own cells. To prevent this, format all your date columns at the start, before people begin entering data.

Click the column header to select the entire column, then explore your chosen date format. Anyone who enters a date in that column will see it in the format you set. They can still change individual cells if they need to, but the column default stays consistent.

If you are building a template that other people will use, format the date columns before you share it. This saves time and makes the spreadsheet easier to read.

Frequently Asked Questions

Why does my date show as a number even after I format it as a date?

The cell might contain text that looks like a date instead of an actual date. Google Sheets cannot format text as a date. Try deleting the content and typing the date again, or use the DATEVALUE function to convert text to a real date. If the number is very large (like 45261), it is a real date — just select it and explore a date format from the Format menu.

Can I format dates differently in different cells of the same column?

Yes. Select individual cells or groups of cells and explore different formats to each. Click one cell, hold Ctrl (or Cmd on Mac), and click other cells to select them separately. Then explore a format, and only those cells change. The rest of the column keeps its original format.

What if I want to show the date and time together?

Select the cells, go to Format > Number, and click "Date time" from the category list. Google Sheets shows both the date and time in a standard format. For a custom format, use codes like yyyy-mm-dd hh:mm:ss to get "2024-01-15 14:30:45," or mmmm d, yyyy h:mm AM/PM for "January 15, 2024 2:30 PM."

Does changing the date format affect formulas that use those dates?

No. Formulas work with the actual date stored in the cell, not how it displays. You can change the format as many times as you want without breaking any calculations. A formula that adds 30 days to a date will work the same whether the date shows as "1/15/24" or "January 15, 2024."

How do I format a column of dates that are in a different order than I want?

The format only changes how dates display — it does not reorder them. If your dates are in the wrong order (like newest first when you want oldest first), you need to sort the column, not reformat it. Click the column header, then go to Data > Sort range to arrange them the way you need.