如何在Microsoft Excel中將文本轉換為日期值

Analysis of business data often requires working with date values in Excel to answer questions such as “how much money did we make today” or “how does this compare to the same day last week?” And that can be hard when Excel doesn’t recognize the values as dates.

對業務數據進行分析通常需要使用Excel中的日期值來回答諸如“我們今天賺了多少錢”或“與上周同一天相比有多少錢？”之類的問題。當Excel無法將值識別為日期時，這可能會很難。

Unfortunately, this is not unusual, especially when multiple users are typing this information, copying and pasting from other systems and importing from databases.

不幸的是，這并不罕見，尤其是當多個用戶鍵入此信息，從其他系統復制和粘貼以及從數據庫導入時。

In this article, we will describe four different scenarios and the solutions to convert the text to date values.

在本文中，我們將描述四種不同的情況以及將文本轉換為日期值的解決方案。

包含句號/日期的日期 (Dates that Contain a Full Stop/Period)

Probably one of the most common mistakes beginners make when typing dates into Excel is doing so with the full stop character to separate the day, month, and year.

初學者在Excel中鍵入日期時可能犯的最常見錯誤之一是使用句號分隔日期，月份和年份。

Excel will not recognize this as a date value and will go ahead and store it as text. However, you can solve this problem with the Find and Replace tool. By replacing the full stops with slashes (/), Excel will automatically identify the values as dates.

Excel不會將其識別為日期值，并將繼續將其存儲為文本。但是，您可以使用“查找和替換”工具解決此問題。通過用斜杠(/)代替句號，Excel將自動將值標識為日期。

Select the columns on which you want to perform the find and replace.

選擇要在其上執行查找和替換的列。

Click Home > Find & Select > Replace—or press Ctrl+H.

單擊主頁>查找并選擇>替換-或按Ctrl + H。

In the Find and Replace window, type a full stop (.) in the “Find what” field and a slash (/) in the “Replace with” field. Then, click “Replace All.”

在“查找和替換”窗口中，在“查找內容”字段中鍵入句號(。)，在“替換為”字段中鍵入斜杠(/)。然后，單擊“全部替換”。

All full stops are converted to slashes and Excel recognizes the new format as a date.

所有句號都將轉換為斜杠，并且Excel會將新格式識別為日期。

Dates with full stops converted to real dates

If your spreadsheet data is regularly changing, and you want an automated solution for this scenario, you could use the SUBSTITUTE function.

如果您的電子表格數據有規律地更改，并且您想要針對這種情況的自動化解決方案，則可以使用SUBSTITUTE函數。

=VALUE(SUBSTITUTE(A2,".","/"))

The SUBSTITUTE function is a text function, so cannot convert it to a date on its own. The VALUE function will convert the text value to a numeric value.

SUBSTITUTE函數是文本函數，因此無法自行將其轉換為日期。 VALUE函數會將文本值轉換為數字值。

The results are shown below. The value needs to be formatted as a date.

結果如下所示。該值需要格式化為日期。

SUBSTITUTE formula to convert text to dates

You can do this using the “Number Format” list on the “Home” tab.

您可以使用“主頁”選項卡上的“數字格式”列表執行此操作。

The example here of a full stop delimiter is typical. But you can use the same technique to replace or substitute any delimiter character.

這里以句點定界符為例。但是您可以使用相同的技術來替換或替換任何定界符。

轉換yyyymmdd格式 (Converting the yyyymmdd Format)

If you receive dates in the format shown below, it will require a different approach.

如果您收到以下所示格式的日期，則將需要使用其他方法。

This format is quite standard in technology as it removes any ambiguity about how different countries store their date values.?However, Excel will not initially understand it.

這種格式在技術上非常標準，因為它消除了有關不同國家/地區如何存儲其日期值的任何歧義。但是，Excel最初不會理解它。

For a quick manual solution, you could use Text to Columns.

對于快速的手動解決方案，您可以使用Text to Columns 。

Select the range of values you need to convert and then click Data > Text to Columns.

選擇您需要轉換的值范圍，然后單擊數據>文本到列。

Text to Columns button from the Data tab

The Text to Columns wizard appears. Click “Next” on the first two steps so that you are at step three, as shown in the image below.?Choose Date and then select the date format being used in the cells from the list. In this example, we are dealing with a YMD format.

出現“文本到列”向導。在前兩個步驟中單擊“下一步”，以便進入第三步，如下圖所示。選擇日期，然后從列表中選擇單元格中使用的日期格式。在此示例中，我們正在處理YMD格式。

Text to Columns to convert 8 digit numbers to dates

If you would like a formula solution, then you could use the Date function to construct the date.

如果需要公式解決方案，則可以使用Date函數構造日期。

This would be used alongside the text functions Left, Mid and Right to extract the three parts of a date (day, month, year) from the cell contents.

這將與文本函數Left，Mid和Right一起使用，以從單元格內容中提取日期的三部分(日，月，年)。

The formula below shows this formula using our sample data.

以下公式使用我們的樣本數據顯示了此公式。

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

Using the DATE formula with 8 digit numbers

Using either of these techniques, you can convert any eight-digit number value. For example, you might receive the date in a ddmmyyyy format or a mmddyyyy format.

使用這些技術中的任何一種，都可以轉換任何八位數字的值。例如，您可能會以ddmmyyyy格式或mmddyyyyy格式接收日期。

DATEVALUE和VALUE函數 (DATEVALUE and VALUE Functions)

Sometimes the problem is not caused by a delimiter character but has an awkward date structure simply because it is stored as text.

有時問題不是由定界符引起的，而是僅由于以文本形式存儲而具有笨拙的日期結構。

Below is a list of dates in a variety of structures, but they are all recognizable to us as a date. Unfortunately, they have been stored as text and need converting.

以下是各種結構中的日期列表，但我們都可以將它們識別為日期。不幸的是，它們已被存儲為文本并且需要轉換。

For these scenarios, it is easy to convert using a variety of techniques.

對于這些情況，可以使用多種技術輕松進行轉換。

For this article, I wanted to mention two functions to handle these scenarios. They are DATEVALUE and VALUE.

對于本文，我想提到兩個函數來處理這些情況。它們是DATEVALUE和VALUE。

The DATEVALUE function will convert text into a date value (probably saw that coming), while the VALUE function will convert text into a generic number value. The differences between them are minimal.

DATEVALUE函數會將文本轉換為日期值(可能已經看到了)，而VALUE函數會將文本轉換為通用數字值。它們之間的差異很小。

In the image above, one of the values contains time information as well. And that will be a demonstration of the functions’ minor differences.

在上圖中，其中一個值也包含時間信息。這將演示功能的微小差異。

The DATEVALUE formula below would convert each one to a date value.

下面的DATEVALUE公式會將每個數字轉換為日期值。

=DATEVALUE(A2)

DATEVALUE function to convert to date values

Notice how the time was removed from the result in row 4. This formula strictly returns just the date value.?The result will still need to be formatted as a date.

請注意，時間是如何從第4行的結果中刪除的。此公式嚴格只返回日期值。結果仍然需要格式化為日期。

The following formula uses the VALUE function.

以下公式使用VALUE函數。