Excel 文本日期：4 種轉換為真實日期的方法

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Excel for Microsoft 365, 2024, 2021, 2019 and 2016 on Windows and Mac. Text to Columns is a desktop feature. The formulas also work in Google Sheets, where the spreadsheet locale (File > Settings > Locale) decides which text formats DATEVALUE understands.

How to tell a date is stored as text

Pick any of these checks:

Microsoft’s documentation adds one more signal: if background error checking is on, text dates with two-digit years get a green triangle. Four-digit text dates usually don’t, so don’t rely on the triangle alone.

To count text dates in a column:

=SUMPRODUCT(--ISTEXT(A2:A10))

Example data

These are typical values from CSV exports, web forms and other systems, all stored as text in column A:

ISO dates (yyyy-mm-dd) are the safest format to import, because there’s no doubt about which part is the month.

Method 1: DATEVALUE (or a double minus)

In B2:

=DATEVALUE(TRIM(A2))

Then format column B as a date: Home > Number Format > Short Date. Without that step you see the serial number (46096 for March 15, 2026). Microsoft’s DATEVALUE page explains this: the function returns a serial number, and “to view a date serial number as a date, you must apply a date format”.

A shorter version that does the same conversion:

=--TRIM(A2)

The double minus forces Excel to treat the text as a number, which works for any text Excel can read as a date.

Guard against mixed columns. If some cells are already real dates, DATEVALUE returns #VALUE! for them because it only accepts text. Keep those cells as they are:

=IF(ISNUMBER(A2), A2, DATEVALUE(TRIM(A2)))

Non-breaking spaces from web pages aren’t removed by TRIM. Swap them for normal spaces first:

=DATEVALUE(TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")))

More on that character: TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160).

When the new column is correct, copy it and paste over the original with Paste Special > Values, then delete the helper column.

Method 2: Text to Columns (in place, and you choose the order)

This is the fastest way to fix a whole column, including dates in the “wrong” order for your settings:

1. Select the column of text dates (one column only).
2. Click Data > Text to Columns.
3. Step 1: choose Delimited, then Next.
4. Step 2: clear all delimiter boxes, then Next.
5. Step 3: under Column data format, choose Date and pick the order your text uses: DMY for 15/03/2026, MDY for 03/15/2026, YMD for 2026-03-15.
6. Click Finish.

Microsoft’s import wizard documentation describes the same step 3 and warns that if the order you pick doesn’t match the data, Excel leaves the column as General instead of converting it. If nothing changes, check the order you chose.

Text to Columns handles 15/03/2026 and 15.03.2026 with DMY. It can’t remove the time part of 2026-03-15T09:30:00Z. Use Method 4 for timestamps.

Method 3: Paste Special > Multiply (in place, no wizard)

For text that DATEVALUE can already read (rows 2, 3, 6, 7 above), you can convert a whole range at once:

1. Type 1 in an empty cell and copy it.
2. Select the text dates.
3. Home > Paste > Paste Special, choose Multiply, click OK.
4. Format the range as a date.

Multiplying by 1 makes Excel read each value as a number, the same way the double minus does. Cells it can’t read stay as they were, so check the result with =COUNT() afterwards.

Method 4: DATE with LEFT, MID and RIGHT (any layout)

When the order or the separators don’t match your settings, take the text apart and give DATE the year, month and day explicitly. DATE(year, month, day) doesn’t depend on regional settings, so this always gives the same result.

Day first, dd/mm/yyyy or dd.mm.yyyy (rows 4 and 10):

=DATE(RIGHT(A4,4), MID(A4,4,2), LEFT(A4,2))

Month first, mm/dd/yyyy, on a computer with day-first settings:

=DATE(RIGHT(A3,4), LEFT(A3,2), MID(A3,4,2))

Compact yyyymmdd (row 5; also works if the value is a real number like 20260315):

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

ISO timestamp with time (row 9, returns March 15, 2026 09:30). Format the result as yyyy-mm-dd hh:mm:

=DATEVALUE(LEFT(A9,10)) + TIMEVALUE(MID(A9,12,8))

The Z means UTC. Excel stores the value without a time zone, so if you need local time, add or subtract the offset, for example +8/24 for UTC+8.

Day and month without leading zeros, like 5/3/2026 (5 March). Fixed positions don’t work here, so use FIND to locate the slashes:

=DATE(RIGHT(A12,4), MID(A12, FIND("/",A12)+1, FIND("/",A12,FIND("/",A12)+1)-FIND("/",A12)-1), LEFT(A12, FIND("/",A12)-1))

In Excel 365, TEXTSPLIT makes this shorter: =LET(p, TEXTSPLIT(A12,"/"), DATE(INDEX(p,3), INDEX(p,2), INDEX(p,1))).

The hidden trap: a column that’s half dates, half text

This is the case most guides miss. You open a CSV of day-first dates (dd/mm/yyyy) on a computer with US (month-first) settings. Excel converts what it can:

• 03/04/2026 (3 April) becomes a real date, March 4, with day and month swapped.
• 15/03/2026 can’t be a month-first date (there’s no month 15), so it stays text.

The column now looks half fixed, but every real date in it is wrong. Running DATEVALUE on the text cells alone leaves the swapped dates in place.

Fix both kinds in one formula: swap the day and month back for real dates, and parse the text ones as day-first:

=IF(ISNUMBER(A2), DATE(YEAR(A2), DAY(A2), MONTH(A2)), DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)))

With this formula, the swapped March 4 becomes April 3, and the text 15/03/2026 becomes March 15. Only use it when you know the source was day-first.

The better long-term fix is to import the file instead of double-clicking it: Data > From Text/CSV (or Text to Columns, Method 2) lets you state the date order before Excel guesses.

Common mistakes

1. Only changing the number format. Formatting a text cell as Date doesn’t convert it. The value is still text.
2. Forgetting to format the result. DATEVALUE returns a serial number like 46096. Apply a date format.
3. Running DATEVALUE on real dates. It returns #VALUE! for cells that are already numbers. Use the ISNUMBER guard.
4. Trusting results when every day is 12 or below. 03/04/2026 converts without an error in both orders, but only one is right. Check a few rows where the day is above 12.
5. Leaving the helper column as formulas. If you delete the original column, the formulas break. Paste the results as values first.

FAQ

How do I know if a date is stored as text in Excel?

Use =ISNUMBER(A2). A real date returns TRUE because Excel stores dates as numbers. Text dates return FALSE and are usually left-aligned.

Why does DATEVALUE return #VALUE!?

Either the text isn’t in a format your Excel settings recognize, such as a day-first date on a month-first system, or the cell already holds a real date. Use DATE with LEFT, MID and RIGHT for the first case, and an ISNUMBER check for the second.

How do I convert dd/mm/yyyy text to a date in Excel?

Use =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)), or select the column and run Data > Text to Columns with Date and DMY in step 3.

How do I convert 20260315 to a date?

Use =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)) and format the result as a date. It works whether the value is text or a number.

Why won’t my dates sort correctly?

Some or all of them are text, which sorts alphabetically. Convert them with one of the four methods, then sort again.

Related tutorials

• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Calculate Age from Date of Birth in Google Sheets (Years, Months, Days)
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes

Live-test findings

On the tested Mac, cleaning a real non-breaking-space date with UNICHAR(160) returned serial 46096; the CHAR-based version failed because CHAR(160) was code 8224. TEXTSPLIT parsed 5/3/2026 as 2026-03-05, serial 46086, using the explicit DMY formula.

Locale boundary: DATEVALUE("03/15/2026") returned #VALUE! in both this Mac environment and Sheets with locale zh_CN. US-setting results in the example are locale illustrations, not live results from these environments. Use explicit DATE construction when the source order is known.

Sources

• Microsoft Support: Convert dates stored as text to dates (left alignment, error checking for two-digit years, DATEVALUE steps, Paste Special Values): https://support.microsoft.com/en-us/office/convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680
• Microsoft Support: DATEVALUE function (returns a serial number, depends on system date settings, time is ignored): https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
• Microsoft Support: Text Import Wizard (Column data format Date with DMY/MDY/YMD; unmatched order stays General): https://support.microsoft.com/en-us/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857
• Microsoft Support: Split text into different columns with the Convert Text to Columns Wizard: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
• Google Docs Editors Help: DATEVALUE (formats depend on region settings; numbers return #VALUE!): https://support.google.com/docs/answer/3093039