Excel如何處理日期出錯？

 在年底忙到昏天黑地的小 E，剛偷偷將自己從繁重的工作解放出來，打算刷個微博放松一下，就看到了 2019 年的放假安排——

本以為是個令人開心的消息，可是……為什么五一勞動節變成了一天假？一天就一天吧，正當我要開始幻想明年假期都去哪里玩時，領導路過了我的位置。

「小 E，上班不工作你干什么呢？上午交的匯總統計表里日期問題很多啊，我一修改完單元格格式，還顯示得亂七八糟，下班前給我重新做一份！」

「好……」小 E 嚇得一個激靈，立馬坐正！欸，之前因為偷懶，簡單匯總了幾份統計表就上交了，結果我的同事們不注意規范日期格式，可是把我坑慘了……

為什么我的日期設置無法正確顯示？

為什么我的公式顯示的值是錯的？

為什么我的日期在公式中找不到對象？

……

上面這些問題背后可能的原因其實都是——我們看到是假日期。

趁著 2019 年還沒到來，小 E 今天就趕緊說說如何把假日期轉化為真日期的方法，不把「假日期」的坑留到明年！

- 1 -

標準日期 YYYYMMDD 的轉換大法

有時我們拿到的數據里，日期樣式會是這樣的：

標準的 YYYYMMDD 格式對吧？但很遺憾，這并不是 Excel 認可的日期格式。

那是不是設置單元格格式為「日期」就可以了呢？那我們來看一下設置為日期格式后的結果：

額，日期竟然全部成了一串#，而且拖動單元格列寬也拯救不了。

Excel 日期其實是一個有區間的數值，上限是 2958465。而這里的 20151012 已經超過了最大日期上限，所以日期轉換失敗。

所以到底該怎么辦呢？

解決方法 1：TEXT 函數法

在 B2 輸入公式如下，向下填充，然后設置 B 列單元格格式為日期。

=--TEXT(A2,"0-00-00")

嘻嘻，搞定！簡單不？不過有些小伙伴肯定會有疑問，小 E 已經替大家準備好了答案——

Q： 為什么會在最前面加--符號？

A：所有用 TEXT 函數處理過的數據返回的都是文本型數據，加上--是為了將文本日期轉換為標準日期，即數值。

Q：為什么上述公式操作完是一串以 4 或者 5 開頭的數字？

A：Excel 里的日期本質是數字序列，1900 年 1 月 1 日開始起，每增加 1 天，數字序列就增加 1，到 2015 年也有 4 萬多天了，所以一開始會顯示成 4 開頭的數字。這時不用著急，選中它們進行格式設置，將數字轉化為日期格式即可。

解決方法 2：分列法

不喜歡用函數？有沒有更簡單的辦法？其實還有一種更簡單、更強大的做法——分列。

分列法能解決大多數的非標準日期問題，小 E 在這里強烈推薦這個辦法喲！

- 2 -

國民日期 YYYY.MM.DD 的轉換大法

下面這樣的日期也是國人比較偏好的一種形式，but，Excel 是老外發明的，這種格式書寫的日期并不被認可為日期格式，而被認為是文本字符串。

我們該怎么對這種日期進行格式轉換呢？

解決方法 1：SUBSTITUTE 函數法

在 B2 輸入如下公式，然后向下填充即可。

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

也就是說，我們只要想個辦法，把國民日期替換成 Excel 認可的日期形式就可以了。

解決方法 2：替換法

這個函數太復雜，記不住，有沒有更簡單的方法？

當然！國民通用的吸心大法，不不不，替換大法也同樣可以實現。

選中日期列，按下快捷鍵【CTRL+H】調出替換功能，在查找內容里輸入.，在替換為里輸入/，最后點擊【全部替換】按鈕，完成替換。

替換后，效果如下：

有些小伙伴又在這里遇到了問題，小 E 這就來解決一下。

Q：為什么我操作的結果有的是對的，有的是錯的，出來的是這個樣子呢？

A：列寬不夠，將日期的列寬拉寬一點就好了，并不是出錯了哦。

- 3 -

文本日期的轉換大法

以上兩種日期格式我們還可以分辨，但有時候，日期的水很深。比如下圖，乍一看是標準日期，沒毛病。但是事實真是如此嗎？

我們用 VLOOKUP 函數來查詢結果，卻發現找不到對象，竟然是#N/A！

因為 E2 單元格是一個標準日期，A 列雖然表面看上去是標準日期，其實卻是一個文本型日期，所以使用 VLOOKUP 進行查找時，找不到匹配的內容。

那么該如何轉換這種日期呢？先告訴大家，改單元格格式為日期這種方法是沒有效果的。文本型的數據通過改單元格格式是無法直接轉換的。

不過小 E 這里有 2 個正確的解決姿勢，這就告訴你——

解決方法 1：公式法

在 C2 輸入公式如下，然后向下填充，最后設置單元格格式為日期即可。

=--A2

單元格格式解決不了的問題，公式可以解決~

解決方法 2：復制粘貼法

不想設置輔助列，糾結，不想用？沒關系，其實簡單的復制粘貼也能幫我們搞定這個問題！

操作步驟如下：

❶ 將日期列（A 列）設置為日期格式

❷ 復制日期列數據

❸ 粘貼到記事本

❹ 復制記事本中的數據

❺ 粘貼數據到 Excel 的日期