在日常工作中，處理數(shù)據(jù)時除了查找、求和等之外，我們還會經常與日期數(shù)據(jù)打交道。

其中我們經常使用的函數(shù)，有 Date（合并日期）、Year（獲取日期的年份）、Month（獲取日期的月份）、Day（獲取日期的天數(shù)）等等。

這不，最近朋友發(fā)來一個數(shù)據(jù)，是從系統(tǒng)里面導出來的。如下圖：

該系統(tǒng)自動將日期與型號混在一起，現(xiàn)在需要將其中的日期提取出來。

提取規(guī)則是：

? 前三個字符代表的是月份數(shù)。? 第 5 和第 6 個字符代表兩位年份數(shù)。? 第 7 和第 8 個字符代表兩位天數(shù)。

提取結果如下圖：

也就是將英文表示的【月-年-日】轉換成純數(shù)字的【年-月-日】形式的日期。

問題分析

我們平時處理的日期大部分是由純數(shù)字組成的，可能很少接觸到這類英文日期。

不過一旦遇到了，我們還是要想辦法解決的，對不對？

比如前面提到的這個案例，我們可以使用下面的函數(shù)來提取出【年-月-日】形式的日期。

=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

結果卻是錯誤值！

為啥？接著往下看你就知道啦！

先來看下公式解析：

? 我們先用 MID(A2,5,2)，提取出年份數(shù)

從該文本的第 5 個字符開始，截取 2 個字符，結果如下圖：

? 再用 LEFT(A2,3)，提取出月份數(shù)

從該文本的第 1 個字符開始，截取 3 個字符，結果如下圖：

? 之后用 MID(A2,7,2)，提取出天數(shù)

從該文本的第 7 個字符開始，截取 2 個字符：

? 最后用 Date 函數(shù)將【年月日】整合在一起。

=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

看上去沒有問題呀？為什么會出現(xiàn)錯誤值#VALUE 呢？

其實，問題就出在月份是英文字母。而不是數(shù)字。

Date 函數(shù)要求【年月日】三個參數(shù)都必須是數(shù)字。（不論是文本型數(shù)字，還是數(shù)值型數(shù)字）

思路有了，下面我們就來看看怎么解決吧！

解決方案

方案 A

將英文月份轉變成數(shù)字，我們可以使用 Match 函數(shù)。

公式如下：

=MATCH("Mar",{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)

公式解析：

MATCH(lookup_value, lookup_array, [match_type])

該函數(shù)有三個參數(shù)：

? 查找值。

第一參數(shù)：就是我們這里要查找的月份，比如："Mar" ，

? 在哪個區(qū)域或者數(shù)組中查找。

第二參數(shù)：我們需要構造一個 1 月-12 月內存數(shù)組：

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

用 Match 函數(shù)去查找第一參數(shù)在第二參數(shù)中的位置，如果在第三個，就返回 3，正好也是 3 月份的意思。

? 精確或者是模糊匹配。

第三參數(shù)：輸入 0，表示精確查找。

最終我們將上面公式整合在一起，結果為：

公式如下：

=DATE(MID(A2,5,2),MATCH(LEFT(A2,3),"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A2,7,2))

公式的第二部分由 Match 函數(shù)組成。返回數(shù)字月份。

這樣結果就出來了！

但是，小伙伴有沒有發(fā)現(xiàn)年份是錯的，Excel 自動給我們加了 19 數(shù)字。

這個還不是我們想要的，所以我們在年份前面加上 20 即可。

公式如下：

=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A2,7,2))

如果小伙伴們不想手動輸入月份的常量數(shù)組，也可以用下面的公式：

公式如下：

=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),TEXT(20&-ROW($1:$12),"mmm"),0),MID(A2,7,2))

其中：

TEXT(20&-ROW($1:$12),"mmm")

構造一個英文月份的常量數(shù)組。這個公式的結果正好是上面的英文月份數(shù)組。

有興趣的小伙伴們可以參考下。

方案 B

另外，除了上面常規(guī)的套路之外，還有一種非常非常簡單的方法，就是下面這種方法。

在【設置單元格格式】==》【數(shù)字】==》【日期】中，查看內置英文日期的顯示方法。

從圖中可以看出：

英文日期的顯示方式是：【日-月-年】

我們可以通過這種方法來組合成日期形式，然后再轉換成目標日期。如下圖：

【B2】公式如下：

=MID(A2,7,2)&"-"&LEFT(A2,6)

公式解析：

? 先用 MID(A2,7,2)提取出天數(shù)：12? 再用 LEFT(A2,6)提取出月和年：Mar-22? 最后用&符號再連接一個日期符號「-」：12-Mar-22

日期樣式已經組合成系統(tǒng)內置的日期形式了。

但是，現(xiàn)在的問題是如何轉換成純數(shù)字的目標日期呢？

其實方法很簡單！

就是進行一次數(shù)學運算即可！如下圖：

【B2】公式如下：

=--(MID(A2,7,2)&"-"&LEFT(A2,6))

公式解析：

先使用一對括號( )放在原公式的外面，再使用兩個負號（--）將其轉換為純數(shù)字日期。

就這樣一個簡單的轉換就實現(xiàn)了我們的需求了，還是挺簡單的吧！

寫在最后

今天，我們分享了如何將英文日期轉換為符合中國人習慣的純數(shù)字日期。

? 使用日期和文本截取函數(shù)的常規(guī)方法將【月-年-日】轉換成【年-月-日】。

? 模擬套用系統(tǒng)內置樣式進行轉換。

可見使用第二種方法，不僅公式非常簡單，而且也大大的提高表格運行效率。

希望小伙伴們在平時的工作中，可以多多留意下系統(tǒng)里面內置的各種設置，肯定能發(fā)現(xiàn)不少好用的功能！

如果你想系統(tǒng)性學習 Excel，掌握更多 Excel 技能。

正好，我們《秋葉 Excel 3 天集訓營》專為職場人準備，全部基于職場真實表格案例設計，還有很多超實用 Excel 技巧教學。

每天學習大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實進階每一步。

秋葉 Excel 3 天集訓營

原價?99 元??

現(xiàn)在?只需 1?元?

每天學習?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報名吧！

??????

好了，今天的分享就到這里。如果對你有幫助，歡迎點個在看或贊支持一下哦~