Column A stores dates as text 2024-03-09 or 20240309. Turn them into real dates

公式

=DATEVALUE(A2)

说明

DATEVALUE parses a date-looking string into an Excel serial. If the text is compact yyyymmdd, slice it with DATE+LEFT/MID instead.

步骤

  1. DATEVALUE works when the string matches a date format Excel recognizes.
  2. Format the result cell as Date.
  3. If DATEVALUE fails, build DATE() from year, month, and day parts.

变体

Compact yyyymmdd

Does not depend on locale separators.

=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

Text with time

VALUE parses date-time; INT drops the time fraction.

=INT(VALUE(A2))

Safe fallback

Tries DATEVALUE first, then yyyymmdd.

=IFERROR(DATEVALUE(A2), DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)))