What you will build

Fix a date column whose cells already contain valid Google Sheets date values but are displayed in an unwanted ISO-style format. You will change only the number format, not the dates themselves.

Learning objective

By the end, you can distinguish a display-format problem from a text-conversion problem, apply the native Date number format to a selected range, and verify that the underlying dates are preserved.

Prepare the sample data

Paste this tab-separated sample beginning at A1, or open the verified practice workbook:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

B2 selected in Google Sheets before changing the date number format; the source date is 2026-09-21.

Open the sample workbook in Google Drive (access required) ↗

Work through the example

Step 1: Select B2:B4. The verified workbook contains real date values already, so do not rebuild them with text functions or overwrite the source values.

Step 2: Open Format > Number and choose Date. This changes only how the selected date values are displayed.

Google Sheets Format menu with Number submenu open and Date visible for the selected B2:B4 range.

Step 3: Keep B2:B4 selected and confirm the display changes to the workbook locale’s standard Date format. In the verified workbook, the results display as 9/21/2026, 9/25/2026, and 10/2/2026.

B2:B4 selected after applying the Date number format, showing 9/21/2026, 9/25/2026, and 10/2/2026.

Checkpoint

CheckExpected result
Selected rangeB2:B4
Menu pathFormat > Number > Date
Verified display9/21/2026; 9/25/2026; 10/2/2026
Underlying valuesThe same three calendar dates; only the display format changes

Date formatting follows the spreadsheet locale, so another locale may show the same dates in a different order or separator style.

Common mistake and correction

If Format > Number > Date does not behave like this example, first confirm whether the source cells are true date values. A text value that only looks like a date must be converted before normal date formatting can work reliably.

Independent practice

Undo the formatting change once, reselect B2:B4, and repeat Format > Number > Date. Confirm that the displayed style changes again while the calendar dates stay the same.

Key takeaways

  • ✓First determine whether the problem is the stored value or only its display format.
  • ✓For valid date values, select the target range and use Format > Number > Date.
  • ✓Changing number format does not change the underlying calendar date.
  • ✓Date display can vary by spreadsheet locale even when the stored date values are identical.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. What should you check before trying to convert a date-looking cell with a formula?
2. Which menu path is verified in this lesson?
3. What changes when you apply Date formatting to valid date values?
4. Why might another spreadsheet show the same dates differently?

Continue learning

Previous lesson: Fix IMPORTRANGE permission errors; Next lesson: Fix slow spreadsheets.