在excel里,80%的職場人錄入的日期都是錯的!
?
作者:苗旭來源:部落窩教育發(fā)布時間:2019-10-08 09:28:41點擊:3424
編按:
哈嘍,大家好!相信大多數(shù)人在剛接觸excel時,在excel中錄入日期的格式都是類似于“xxxx.xx.xx”的形式,以“.”作為年月日的分隔。但是這樣的日期卻不符合excel日期的填寫規(guī)范,是錯誤的日期。今天我們就來認(rèn)識下excel中日期的填寫規(guī)范以及怎樣將錯誤的日期格式改為正確的日期格式,趕緊來看看吧!
小麗:“苗老師,你說我輸入的這個日期要怎么變成帶有年月日的日期呀?”
苗老師:“修改一下日期格式就好了?!?/span>
小麗:“不行呀,我剛試過了,改了還是原來的樣子?!?/span>
苗老師:“表發(fā)我看看?!?/span>
小麗:“好!”,如圖1所示:
圖1
苗老師:“哦,你這個格式不是日期格式呀,Excel是不認(rèn)的,改格式自然也無從談起?!?/span>
小麗:“那怎么辦呢?”
苗老師:“你把‘.’批量替換成‘/’或者是‘-’,就行啦?!比?span>GIF2所示。
GIF2
修改完后,就可以改成帶有年月日的日期格式了。如圖GIF3所示。
GIF3
小麗:“為什么你寫的日期就可以任意改格式,而我寫的卻不能?”
苗老師:“這里就要介紹一下Excel里的真日期和假日期了?!?/span>
我把Excel里可以任意修改日期格式,并且可運算的日期叫做真日期,其他的叫做假日期。例如下面都是常見的輸入日期的方式,這里面有的是真日期,有的是假日期。
圖4
很多人就喜歡用“2019.1.1”這樣的格式,雖然符合國人的填寫習(xí)慣,卻不符合Excel日期的填寫規(guī)范。想要把假日期“2019.1.1”變?yōu)檎嫒掌冢丝梢允褂蒙鲜稣f到的查找替換解決,還可以使用SUBSTITUTE函數(shù)實現(xiàn)。
公式:=SUBSTITUTE(A1,".","-"),如圖5所示。
圖5
那么類似于“20190101”這樣的假日期,怎樣才能變?yōu)檎嫒掌谀兀课覀兛梢允褂煤瘮?shù)來解決。
公式:=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
別看這么長一串公式就覺得很復(fù)雜,主要就是兩個部分,DATE是一個日期格式的函數(shù)。我們先用LEFT,MID,RIGHT三個函數(shù)分別提取出假日期中的年月日,再分別帶入到DATE函數(shù)的year、month、day參數(shù)中即可。如圖6所示。
圖6
這里再舉一個關(guān)于格式問題的例子。如圖7所示,下面的日期雖然看上去是真日期,但實際上卻是不可計算、無法任意改變格式的文本格式。我們這里用ISTEXT函數(shù)來檢測它,這個函數(shù)是用來判斷單元格中的值是否為文本,文本則顯示“TRUE”,不是文本則顯示“FALSE”。
圖7
當(dāng)然,這里我們也可以取消單元格居中的顯示方式,通過excel中“文本靠左,數(shù)字靠右”這一定律,來判斷單元格中數(shù)據(jù)的格式。(注意:日期時間是一種特殊的數(shù)值,可以直接參與計算)
GIF8
苗老師:“那怎么才能將上述這種情況修改為正確的日期格式呢?”
小麗:“直接把格式改成日期不就好了嗎?”
苗老師:“那是不行的,將Excel中的文本格式修改成數(shù)值或是日期格式,需要挨個雙擊單元格后才能生效。所以我們要用另一種辦法來強制改變單元格格式,那就是excel中的分列功能?!?/span>
分列的本來作用是將一列數(shù)據(jù)使用某個符號或是以某個位置進(jìn)行分列,如GIF9所示。
GIF9
不知道大家有沒注意到GIF9中最后點確定的那個界面,就是我們用來修改格式的界面,如圖10所示。
圖10
我們可以不設(shè)置前面分列的內(nèi)容,直接點擊“下一步”跳到最后一個頁面,將格式改為日期,就可以得到正確的日期格式了。如GIF11所示。
GIF11
這里還有一種情況要說明一下,前面說到日期時間是一種特殊的數(shù)值,可以直接參與計算。其實在Excel里每個日期都有其對應(yīng)的數(shù)字,如果我們把真日期的格式改為常規(guī)或是數(shù)值,就會發(fā)現(xiàn)日期全變成了數(shù)字。如GIF12所示。
GIF12
所以當(dāng)你發(fā)現(xiàn)日期變成了一串?dāng)?shù)字時,千萬別緊張,只要把格式修改成日期格式就可以變回來了。
“小麗你明白了嗎?”
小麗:“苗老師,你說了這么多,我都聽懂了,可是我們經(jīng)理就喜歡看‘2019.1.1’這樣的日期,有沒有辦法既能保證運算,又能保留點格式的日期呢?”
苗老師:“我說了一大堆又繞回來了。好吧,這也不難,設(shè)置excel中的‘自定義格式’就能搞定?!?/span>
我們平時使用的格式,無非是常規(guī) 、日期、文本、百分比等等。如圖13所示。
圖13
“自定義格式”用的比較少,不過它的作用可一點兒都不小。我們可以使用它制定很多想要的格式,在這里我就介紹一下怎么使用它制定需要的日期格式。比如我們要設(shè)置“2019.1.1”這樣的格式,只需要在“自定義格式”里輸入“yyyy.m.d”就行了。其中y代表年,4個y就表示4位數(shù)的年,m表示月,d表示日,然后中間用點號隔開。如圖14所示。
圖14
這樣一來,數(shù)據(jù)在單元格中顯示的內(nèi)容就是我們需要的格式了,但在編輯欄中顯示的仍然是我們原本輸入的正確的日期格式。
總結(jié):日期格式是我們在日常工作中經(jīng)常打交道的一個格式,如何用好它,在工作中也是非常重要的,判斷日期格式,修改格式等等都是常見的問題,小伙伴們學(xué)會了嗎?
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
分列《輸入日期最快的方式竟然是數(shù)據(jù)分列!》
13種日期輸入的方法《13種日期輸入法你只會第1種手動輸入?》
查找替換的妙用《日期數(shù)據(jù)無法篩選?查找替換幫大忙》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!