☰

Excel文字轉換日期格式

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
Excel文字轉換日期格式

我有一批包含幾列的數據，其日期格式如下：

2014 年 5 月 31 日。

我正在嘗試找出一種方法將其轉換為有用的日期格式 05/31/2014。有任何想法嗎？

答案1

有幾個步驟可以實現這一目標。這是使用公式和輔助單元格來完成此操作的一種方法。如果您需要在沒有輔助單元的情況下執行此操作（即您不需要額外的列），那麼您應該提及這一點，我可能可以編寫一些 VBA 來完成該任務。

第一個任務是將日期分為 3 個部分：年、月、日。一旦你擁有了這三個，那麼將其重新組合起來就很簡單了。

這當然是假設你的日期總是遵循相當愚蠢的美國日期格式© MMM-dd-yyyy。

這是使用 Microsoft 頁面完成的使用函數在列之間拆分文本舉個例子。

  1. 分割月份：
    這是透過使用search尋找第一月份的功能和將其剪切出來的 -分割工具來實現的。Left

    =LEFT(A1, SEARCH("-",A1,1)-1)

    May從你的例子中返回。

  2. Splitting out the day：類似，但需要
    更複雜地使用該功能。MID我們需要找到第一個和第二個-，然後用第二個減去第一個的位置來得到所需文本的長度，這個結果是一個相當長的函數：

    =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

    返回SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)長度，而SEARCH("-",A1,1)+1只是獲取第一個位置。該MID工具會將它們切掉並23從您的範例中返回。

  3. 劃分年份：
    我們需要的最後一個工具是RIGHT工具。

    =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

    這只是獲取字串的長度，然後搜尋兩次並返回左側（或右側）的所有內容。

現在的問題是您仍然擁有無法使用的文字格式的月份。為了使其可用，我們需要使用DATEVALUE工具對其進行轉換。該工具本身不接受月份，但會接受字串MMM YY。我們能做的就是在將年份連接到月份文字後將其解析為數字。

  1. 以數值形式取得月份：
    為了方便論證，假設我們將第一個函數儲存在儲存格 B1 中。
    DATEVALUE(B1 &" 01")將返回 excel 內部使用的日期值字串，然後我們可以將其包裝在函數中MONTH以返回月份的數值：

    MONTH(DATEVALUE(B1 &" 01"))

  2. 然後，我們可以將所有這些合併到一個DATE(year,month,day)函數中，從而將其轉換為 Excel 可以使用的真實日期。月份位於儲存格 B1 中，日期位於儲存格 C1 中，年份位於 D1 中，遵循愚蠢的美國日期格式™：

    DATE(D1, B1, C1)

    然後將返回23/05/2014牢房。

  3. 如果您想要真正喜歡，您可以繼續將上述所有公式合併為一個。

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

答案2

另一種簡單的方法是採用類似的字串2015 年 5 月 31 日並將其轉換為2015 年 5 月 31 日。這種轉換將允許我們使用日期值()公式。

所以數據在A1， 在B1進入：

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

在此輸入影像描述

答案3

我知道問題已經得到解答，我感謝所有的意見。我想添加這個其他選項，以防萬一任何人有幫助：

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

這會將日期從 May-31-2014 格式轉換為 5/31/2014 格式。請注意，在我的具體情況下，僅使用三個字母輸入月份（例如 Jul）

謝謝！

相關內容