What you will build

You will repair imported date text with DATEVALUE, fill the formula down for the remaining rows, format the results as dates, and verify that the repaired values still respond correctly when the source data changes.

Learning objectives

Open the 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.

Sample data

Raw dateConverted date
2026-09-28
2026-09-29
2026-09-30

Keep A2:A4 as text to reproduce the issue; column B is reserved for DATEVALUE results.

Google Sheets practice result workbook before conversion, with the Raw date text values selected and Converted date cells still blank.

Hands-on practice

Step 1: Confirm A2:A4 contain date-looking text values rather than calculated date serials.

Step 2: Enter =DATEVALUE(A2) in B2 and fill the formula down through B4. Select B2:B4, then choose Format > Number > Date.

Step 3: Confirm B2:B4 display 9/28/2026, 9/29/2026, and 9/30/2026 in this en_US workbook. The cells remain formula results and can now be used for chronological sorting.

=DATEVALUE(A2)
Google Sheets with B2 selected after entering =DATEVALUE(A2), showing the conversion formula and result.
Google Sheets Format menu opened while the converted date range is selected, as part of applying a date number format.

Enter the formula once in B2, then fill it down through B4. With B2:B4 selected, use Format > Number > Date so the serial values display as dates.

Checkpoint

Raw dateConverted date
2026-09-289/28/2026
2026-09-299/29/2026
2026-09-309/30/2026

A2:A4 remain the original text strings. B2:B4 must be DATEVALUE formula results formatted as dates, not manually typed dates.

Google Sheets final checkpoint with converted dates displayed after the DATEVALUE formulas are filled down and formatted as dates.

Common mistakes and fixes

Correction: keep A2:A4 unchanged, correct the text or formula in the converted column, then verify B2:B4 are formula results and display as dates.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

  • ✓A value that looks like a date is not necessarily stored as a real date.
  • ✓DATEVALUE converts recognized date text into a Google Sheets date serial.
  • ✓Enter =DATEVALUE(A2) once in B2 and fill the formula through B4.
  • ✓Use Format > Number > Date on B2:B4; the exact display depends on the workbook locale.
  • ✓Temporarily change one source value to confirm the formula responds correctly, then restore the sample.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. What is the correct first action in “Fix dates imported as text”?
2. Which follow-up action belongs to “Fix dates imported as text”?
3. What should you verify before “Fix dates imported as text” is complete?
4. What should you do if DATEVALUE returns a number instead of a readable date?

Next lesson

Continue in recommended learning order with Sort and filter entries in Weekly study plan.

Next: Fix leading zeros disappearing ↗