原文標題:《比你笨法快 10 倍，這才是 Excel 中最牛的日期數據整理方法！》

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

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

這不，最近朋友發來一個數據，是從系統里面導出來的。如下圖:

該系統自動將日期與型號混在一起，現在需要將其中的日期提取出來。

提取規則是:

? 前三個字符代表的是月份數。

? 第 5 和第 6 個字符代表兩位年份數。

? 第 7 和第 8 個字符代表兩位天數。

提取結果如下圖:

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

問題分析

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

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

比如前面提到的這個案例，我們可以使用下面的函數來提取出形式的日期。

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

結果卻是錯誤值！

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

先來看下公式解析:

? 我們先用 MID ，提取出年份數

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

? 再用 LEFT ，提取出月份數

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

? 之后用 MID ，提取出天數

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

? 最后用 Date 函數將整合在一起。

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

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

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

Date 函數要求三個參數都必須是數字。(不論是文本型數字，還是數值型數字)

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

解決方案方案 A

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

公式如下:

=MATCH

公式解析:

MATCH)

該函數有三個參數:

? 查找值。

第一參數:就是我們這里要查找的月份，比如:"Mar" ，

? 在哪個區域或者數組中查找。

第二參數:我們需要構造一個 1 月-12 月內存數組:

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

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

? 精確或者是模糊匹配。

第三參數:輸入 0，表示精確查找。

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

公式如下:

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

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

這樣結果就出來了！

但是，小伙伴有沒有發現年份是錯的，Excel 自動給我們加了 19 數字。

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

公式如下:

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

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

公式如下:

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

其中:

TEXT,"mmm")

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

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

方案 B

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

在》(數字)》(日期)中，查看內置英文日期的顯示方法。

從圖中可以看出:

英文日期的顯示方式是:

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

公式如下:

=MIDamp;"-"amp;LEFT(A2,6)

公式解析:

? 先用 MID 提取出天數:12

? 再用 LEFT 提取出月和年:Mar-22

? 最后用 amp; 符號再連接一個日期符號「-」:12-Mar-22

日期樣式已經組合成系統內置的日期形式了。

但是，現在的問題是如何轉換成純數字的目標日期呢？

其實方法很簡單！

就是進行一次數學運算即可！如下圖:

公式如下:

=--amp;"-"amp;LEFT(A2,6))

公式解析:

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

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

寫在最后

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

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

? 模擬套用系統內置樣式進行轉換。

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

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