如何修改Excel中的非法日期

我們在使用Excel的時候，有很多人對日期數據的輸入是很隨便的，有時輸入的“日期”并不是真正的日期格式。而從數據庫導入的數據中，很多情況下的日期也并不是真正的日期，而是文本。因此，不論什么時候拿到什么樣的表格。首先要檢查表格中的日期是否為真正的日期。今天我們就來學習下如何修改Excel中的非法日期。

在Excel中，日期是作為數值存儲在單元格中的。因此，盡管顯示格式多種多樣，它們實際上都是數值。

Excel將日期存儲為序列號（稱為序列值）。默認情況下，1900年1月1日是序列號1.依此類推。2表示1900年1月2日。3表示1900年1月3日…一40 278就是2010年4月10日。在處理日期數據時。記住一點就可以了：日期數字永遠是正整數。

因此，假如在單元格中輸入了一個數字40 278.將其轉換為日期。就表示2010年4月10日。

Excel所能處理的日期最小值是1900年1月0號（對應數字0）。日期最大值是9999年12月31號（對應數字2 958—465）。0-2 958 465之間的數字都可以轉換為臺法的日期和時間。如果輸入的數字超出了這個區間。就不能轉換為合法日期。而是非法的日期了。

例如在單元格中輸入20100410，盡管它是一個數字。但超過了Excel所能處理的最大日期數字2 958 465。

了解了Excel處理日期的基本規則。在輸入日期時就需要按照正確的格式輸入。輸入日期的正確格式是年、月、日3個數字之間用減號（-）或者斜杠（/）隔開。倒如。正確的日期輸入格式為2010-4-10、2010t/4/10、10/Apt/2010或者10-Apt-2010。但是。如果輸入2010.04.10或者20100410就大錯特錯了。

用戶可以按照習慣采用一種簡單的方法輸入日期。倒如。要輸入日期2010-4-10.下面的任何一種方法都是可行的：

鍵入“2010-4-10”；

·鍵入“2010/4/10”：

·鍵入“2010年4月10日”：

·鍵入“4-10”；

·鍵入“4/10”：

·鍵入“4月10日”：

·鍵入。10-4-10”：

·鍵入“10/4/10”；

·鍵入“10-Apr-10”：

·鍵入“10一Apr一2010”；

·鍵入“10一Apr”；

·鍵入“Apt一10”。

此外，Excel接受采用兩位數宇輸入的年份。但會進行不同的處理。

·00—29：Excel將00—29之間兩位數字的年解釋為2000-2029年。例如。輸入日期

“19-5-28.Excel假定該日期為2019年5月28日。

·30—99：Excel將30—99之間兩位數字的年解釋為1930-1999年。例如。輸入日期

“98-5-28”。Excel將假定該日期為1998年5月28日。

如果工作表中存在有大量的非法日期。就必須先將這些非法日期修改為真正的日期。根據實際情況?？梢圆捎貌煌姆椒?。下面舉例進行說明。

在很多情況下。某列的日期數據都是非法日期?；蛘叽蟛糠质欠欠ㄈ掌?。這主要發生在從數據庫導入數據的場合。此時。一十一個地修改單元格是不現實的?？梢允褂?。分列。工具批量修改非法日期。

圖1所示為一些非法日期的例子。將這些非法日期轉換為真正日期的量簡便方法。是利用“分列”工具，下面介紹其具體方法和步驟。

圖1

1、選擇B列數據。注意每次只能選擇一列數據。

2、單擊“數據”選項卡。再單擊“數據工具”功能組中的“分列”按鈕，如圖2所示。

圖2

此時會打開“文本分列向導-步驟之1（共3步）”話框，如圖3所示。

圖3

3、單擊兩次“下一步”按鈕，打開“文本分列向導-步驟之3共3步”對話框，在“列散據格式”選項組中選擇“日期”單選按鈕，在右側的下拉列表框中選擇日期格式“YMD”，如圖4所示。

圖4

4、單擊“完成”按鈕，即可將B列的非法日期轉換為真正的日期。

其他列非法日期的轉換也是采用這種方法如法炮制。最后的結果如圖5所示。

圖5

需要注意的是，在轉換J列的非法日期時，在“文本分列向導-步驟之3（共3步）對話框中，要在日期格式下拉列表框中要選擇“MDY”格式，因為原始數據的格式是“月-日-年”。

有些人在設計合同管理表格時。會把合同的起始日期和截止日期放在一起。如圖6所示。這種表格結構盡管看起來比較緊湊。數據表示得也比較清楚。但是非常不利于數據分析。倒如。無法把某年某月簽訂合同或者續簽合同要到期的員工篩選出來。也無法設計一個臺同到期提前提醒功能。

圖6

規范的表格應該是把合同的起始日期和截止日期分成兩列保存。而不是圖6所示的那樣保存在一列中。如果已經做成了這樣的表格。怎樣將表格整理成規范的表格呢？

仔細觀察表格數據?？梢钥闯鋈掌跀祿潜容^標準和有規則的。都是諸如“2006-09-01”這樣的數據。它有10個宇符。這樣就可以使用LEFT函數和RIGHT函數把合同的起始日期和截止日期分別取出來。具體步驟如下：

1、重斯設計表格，在第一行下面插入一行，在E列右側插入兩列，在H列右側插入兩列，輸入標題，并合并有關的單元榕，如圖7所示。

圖7

2、在單元格F3中輸入公式“=DATEVALUE（LEFT（E3，10））”，并向下復制，得到第一次合同的開始日期。在單元格C3中輸入公式“=DATEVALUE（RIGHT（E3，10））”，并向下復制，得到第一次臺同的截止日期。

3、在單元格I3中輸入公式“=IF（H3=""，""，DATEVALUE（LEFT（H3，10）））”，并向下復制，得到續簽合同的開始日期。

在單元格J 3中輸入公式“=IF（H3=""，""，DATEVALUE（RIGHT（H3，10）））”，并向下復制得到續簽合同的截止日期。結果如圖8所示。

圖8

注意：利用文本函數LEFT和RIGHT得到的日期數據是文本日期，因此在公式中還必須使用DATEVALUE函數將這個文本日期轉換為真正的日期。

4、采用選擇性粘貼的方法，將單元格中的公式轉換為數值，方法是：選擇整個數據區域，按【Ctrl+C】組合鍵復制，再右擊要粘貼的位置，在彈出的快捷菜單中選擇“選擇性粘貼”命令，打開“選擇性粘貼”對話框。選擇“數值”單選按鈕，如圖9所示。單擊“確定”按鈕。

圖9

5、刪除原始的兩列合同日期。即可得到規范的合同管理表格，如圖1 0所示。 

圖10

雖然我們學會了如何修改Excel中的非法日期，但是我們最好還是在建立表格的時候就創建正確的日期格式，這樣我們就不用在后面進行修復日期了，這樣對我們做數據分析也有很大的好處。