來(lái)源丨秋葉Excel（ID：excel100）
作者丨無(wú)敵 - Excel 研究院

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

本以為是個(gè)令人開(kāi)心的消息，可是……為什么五一勞動(dòng)節(jié)變成了一天假？一天就一天吧，正當(dāng)我要開(kāi)始幻想明年假期都去哪里玩時(shí)，領(lǐng)導(dǎo)路過(guò)了我的位置。

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

「好……」小 E 嚇得一個(gè)激靈，立馬坐正！欸，之前因?yàn)橥祽?，?jiǎn)單匯總了幾份統(tǒng)計(jì)表就上交了，結(jié)果我的同事們不注意規(guī)范日期格式，可是把我坑慘了……

上面這些問(wèn)題背后可能的原因其實(shí)都是——我們看到是假日期。

趁著 2019 年還沒(méi)到來(lái)，小 E 今天就趕緊說(shuō)說(shuō)如何把假日期轉(zhuǎn)化為真日期的方法，不把「假日期」的坑留到明年！

標(biāo)準(zhǔn)日期 YYYYMMDD 的轉(zhuǎn)換大法

有時(shí)我們拿到的數(shù)據(jù)里，日期樣式會(huì)是這樣的：

標(biāo)準(zhǔn)的 YYYYMMDD 格式對(duì)吧？但很遺憾，這并不是 Excel 認(rèn)可的日期格式。

那是不是設(shè)置單元格格式為「日期」就可以了呢？那我們來(lái)看一下設(shè)置為日期格式后的結(jié)果：

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

Excel 日期其實(shí)是一個(gè)有區(qū)間的數(shù)值，上限是 2958465。而這里的 20151012 已經(jīng)超過(guò)了最大日期上限，所以日期轉(zhuǎn)換失敗。

所以到底該怎么辦呢？

 解決方法 1：TEXT 函數(shù)法 

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

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

嘻嘻，搞定！簡(jiǎn)單不？不過(guò)有些小伙伴肯定會(huì)有疑問(wèn)，小 E 已經(jīng)替大家準(zhǔn)備好了答案——

Q： 為什么會(huì)在最前面加--符號(hào)？

A：所有用 TEXT 函數(shù)處理過(guò)的數(shù)據(jù)返回的都是文本型數(shù)據(jù)，加上--是為了將文本日期轉(zhuǎn)換為標(biāo)準(zhǔn)日期，即數(shù)值。

Q：為什么上述公式操作完是一串以 4 或者 5 開(kāi)頭的數(shù)字？

A：Excel 里的日期本質(zhì)是數(shù)字序列，1900 年 1 月 1 日開(kāi)始起，每增加 1 天，數(shù)字序列就增加 1，到 2015 年也有 4 萬(wàn)多天了，所以一開(kāi)始會(huì)顯示成 4 開(kāi)頭的數(shù)字。這時(shí)不用著急，選中它們進(jìn)行格式設(shè)置，將數(shù)字轉(zhuǎn)化為日期格式即可。

 解決方法 2：分列法 

不喜歡用函數(shù)？有沒(méi)有更簡(jiǎn)單的辦法？其實(shí)還有一種更簡(jiǎn)單、更強(qiáng)大的做法——分列。

分列法能解決大多數(shù)的非標(biāo)準(zhǔn)日期問(wèn)題，表姐在這里強(qiáng)烈推薦這個(gè)辦法喲！

國(guó)民日期 YYYY.MM.DD 的轉(zhuǎn)換大法

下面這樣的日期也是國(guó)人比較偏好的一種形式，but，Excel 是老外發(fā)明的，這種格式書(shū)寫(xiě)的日期并不被認(rèn)可為日期格式，而被認(rèn)為是文本字符串。

我們?cè)撛趺磳?duì)這種日期進(jìn)行格式轉(zhuǎn)換呢？

 解決方法 1：SUBSTITUTE 函數(shù)法 

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

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

也就是說(shuō)，我們只要想個(gè)辦法，把國(guó)民日期替換成 Excel 認(rèn)可的日期形式就可以了。

 解決方法 2：替換法 

這個(gè)函數(shù)太復(fù)雜，記不住，有沒(méi)有更簡(jiǎn)單的方法？

當(dāng)然！國(guó)民通用的吸心大法，不不不，替換大法也同樣可以實(shí)現(xiàn)。

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

替換后，效果如下：

有些小伙伴又在這里遇到了問(wèn)題，小 E 這就來(lái)解決一下。

Q：為什么我操作的結(jié)果有的是對(duì)的，有的是錯(cuò)的，出來(lái)的是這個(gè)樣子呢？

A：列寬不夠，將日期的列寬拉寬一點(diǎn)就好了，并不是出錯(cuò)了哦。

文本日期的轉(zhuǎn)換大法

以上兩種日期格式我們還可以分辨，但有時(shí)候，日期的水很深。比如下圖，乍一看是標(biāo)準(zhǔn)日期，沒(méi)毛病。但是事實(shí)真是如此嗎？

我們用 VLOOKUP 函數(shù)來(lái)查詢(xún)結(jié)果，卻發(fā)現(xiàn)找不到對(duì)象，竟然是#N/A！

因?yàn)?E2 單元格是一個(gè)標(biāo)準(zhǔn)日期，A 列雖然表面看上去是標(biāo)準(zhǔn)日期，其實(shí)卻是一個(gè)文本型日期，所以使用 VLOOKUP 進(jìn)行查找時(shí)，找不到匹配的內(nèi)容。

那么該如何轉(zhuǎn)換這種日期呢？先告訴大家，改單元格格式為日期這種方法是沒(méi)有效果的。文本型的數(shù)據(jù)通過(guò)改單元格格式是無(wú)法直接轉(zhuǎn)換的。

不過(guò)小 E 這里有 2 個(gè)正確的解決姿勢(shì)，這就告訴你——

 解決方法 1：公式法 

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

=--A2

單元格格式解決不了的問(wèn)題，公式可以解決~

 解決方法 2：復(fù)制粘貼法 

不想設(shè)置輔助列，糾結(jié)，不想用？沒(méi)關(guān)系，其實(shí)簡(jiǎn)單的復(fù)制粘貼也能幫我們搞定這個(gè)問(wèn)題！

操作步驟如下：

? 將日期列（A 列）設(shè)置為日期格式

? 復(fù)制日期列數(shù)據(jù)

? 粘貼到記事本

? 復(fù)制記事本中的數(shù)據(jù)

? 粘貼數(shù)據(jù)到 Excel 的日期