904 |
Excel中日期與時間的秘密,全在這裡了 |
||||
示範檔 |
無 |
範例檔 |
904.XLSX |
結果檔 |
904F.XLSX |
PS:現在是凌晨5點,我要趕10點的飛機去香港參加一個老同事的婚禮,我們已經六年多沒有相見了,再見不知道會是什麼情景。時間是最無情,一天天,一年年,我們慢慢成熟,我們漸漸變老,時間也最是有情,那些沉澱下來的美好的人和事兒,想起來嘴角都會上揚。
所以,現在,我想和大家分享日期和時間的秘密。
一、日期和時間的輸入
日期和時間輸入有三種方法,一是直接輸入,二是快速鍵輸入,三是公式函數輸入。
第一種方法必須要掌握Excel能夠接受的資料格式,才能正確輸入;
第二種方法最高效率且最能節省時間,一次輸入,永不改變;
第三種方法輸入後,每次打開Excel表格或者重算儲存格,日期和時間都會發生變化,更新為當下的日期和時間,因此,公式輸入經常用在日期和時間的計算中。
另外,我們需要注意的是:
在Excel中,日期和時間是以一種特殊的數值形式存儲的。
Excel是機器語言,預設為1900年1月1日是第1天,最大日期為9999年12月31日,即第2958465天。默認為時間1天24小時等於數值「1」,1小時可以用1/24來表示,1分鐘可以用1/(24×60)來表示,以此類推,一天中的每一個時刻都可以以小數形式來表示。
所以,當2018年10月28日等於43401,6點半等於0.27,你還會覺得奇怪嗎?
二、日期和時間格式的整理
1、不規範的日期和時間格式
2018.8.25和12.2這樣的格式是不規範的日期和時間格式,可是如果我們遇到了類似的問題該怎麼辦呢?不用著急,我們可以直接用Ctrl+H把“.”變成“/”和半形“:”,這樣格式就符合規定啦。
2、將日期與時間分離
同樣可以用到分欄,最後將帶時間的日期格式轉換為只有日期的格式,即完成了分欄。
步驟1:將分欄的資料範圍框選起來(A2:A15)。
步驟2:點取「資料 > 資料剖析」圖示,如下圖所示。
步驟3:在【資料剖析精靈】對話方塊,點取「固定寬度」選項鈕,如下圖所示。
步驟4:點取「下一步」鈕。
步驟5:在「預覽分欄結果」列示方塊,拉出分隔線,如下圖所示。
步驟6:點取「下一步」鈕。
步驟7:點取二個欄都設定為日期格式。
步驟8:點取「目標儲存格」欄位,輸入「B2」,如下圖所示。
步驟9:點取「完成」鈕,結果如下圖所示。
3、日期和時間的合併
因為日期是整數,時間是小數,因此日期和時間的合併只要相加即可。
如果是單獨提取時間和日期的話就用減法,日期&時間-日期=時間,日期&時間-時間=日期。
三、日期的計算
(1)TODAY、NOW、YEAR、MONTH、DAY、WEEKDAY函數
這是一組非常簡單但是又最為常用的日期函數,通常與其他嵌套使用,實現更多樣化的結果。
案例:
假設情況下,男70歲退休,女65歲退休(政策有變,先不予考慮)。我們可以根據員工的出生日期和性別來確定退休日期。
D2儲存格公式「=DATE(YEAR(C2)+(B2="男")*5+65,MONTH(C2),DAY(C2))」。
(2)DATEDIF函數
DATEDIF函數是Excel的一個隱藏函數,不在插入函數的函式程式庫裡,在Excel輔助檔也沒有它的身影,通常情況下都是直接輸入,是非常重要的日期函數。
基本用法:
現有兩個日期,通過DATEDIF函數計算兩個日期之間的天數、月數和年數,計算結果及結果說明如下。
我可以用DATEDIF函數給即將結婚的玲玲寫了一封信:
親愛的玲玲:
今天是=TODAY()=2018年8月24日,距離我們上一次相見(2012年6月1日)已經過去=DATEDIF(開始日期,今天的日期,"Y")=6年了,相當於=DATEDIF(開始日期,今天的日期,"M")=74月了,當然代表我們已經有=DATEDIF(開始日期,今天的日期,"D")=2275天沒有相見了,真心祝福你在人生剩下的=DATEDIF(今天的日期,100歲的日期,"D")=25567天裡一切順心,幸福美滿!
四、時間的計算
計算時間間隔的小時、分鐘和秒
間隔的小時=(結束時間-開始時間)*24
間隔的分鐘=(結束時間-開始時間)*24*60
間隔的秒=(結束時間-開始時間)*24*60*60
相關主題:
留言列表