close

904

Excel中日期與時間的秘密,全在這裡了

示範檔

範例檔

904.XLSX

結果檔

904F.XLSX

PS:現在是凌晨5點,我要趕10點的飛機去香港參加一個老同事的婚禮,我們已經六年多沒有相見了,再見不知道會是什麼情景。時間是最無情,一天天,一年年,我們慢慢成熟,我們漸漸變老,時間也最是有情,那些沉澱下來的美好的人和事兒,想起來嘴角都會上揚。

所以,現在,我想和大家分享日期和時間的秘密。

一、日期和時間的輸入

日期時間輸入有三種方法,一是直接輸入二是快速鍵輸入三是公式函數輸入

第一種方法必須要掌握Excel能夠接受的資料格式,才能正確輸入;

第二種方法最高效率且最能節省時間,一次輸入,永不改變;

第三種方法輸入後,每次打開Excel表格或者重算儲存格,日期和時間都會發生變化,更新為當下的日期和時間,因此,公式輸入經常用在日期和時間的計算中。

另外,我們需要注意的是:

Excel中,日期時間是以一種特殊的數值形式存儲的。

Excel是機器語言,預設為190011是第1天,最大日期為99991231,即第2958465天。默認為時間124小時等於數值「1」,1小時可以用1/24來表示,1分鐘可以用1/24×60)來表示,以此類推,一天中的每一個時刻都可以以小數形式來表示。

所以,當20181028日等於434016點半等於0.27,你還會覺得奇怪嗎?

二、日期和時間格式的整理

1、不規範的日期和時間格式

2018.8.2512.2這樣的格式是不規範的日期和時間格式,可是如果我們遇到了類似的問題該怎麼辦呢?不用著急,我們可以直接用Ctrl+H“.”變成“/”和半形“:”,這樣格式就符合規定啦。

2、將日期與時間分離

同樣可以用到分欄,最後將帶時間的日期格式轉換為只有日期的格式,即完成了分欄。

步驟1:將分欄的資料範圍框選起來(A2:A15)

步驟2:點取「資料 > 資料剖析」圖示,如下圖所示。

步驟3:在【資料剖析精靈】對話方塊,點取「固定寬度」選項鈕,如下圖所示。

步驟4:點取「下一步」鈕。

步驟5:在「預覽分欄結果」列示方塊,拉出分隔線,如下圖所示。

步驟6:點取「下一步」鈕。

步驟7:點取二個欄都設定為日期格式。

步驟8:點取「目標儲存格」欄位,輸入「B2」,如下圖所示。

步驟9:點取「完成」鈕,結果如下圖所示。

3、日期和時間的合併

因為日期是整數,時間是小數,因此日期和時間的合併只要相加即可。

如果是單獨提取時間和日期的話就用減法,日期&時間-日期=時間,日期&時間-時間=日期。

三、日期的計算

1TODAYNOWYEARMONTHDAYWEEKDAY函數

這是一組非常簡單但是又最為常用的日期函數,通常與其他嵌套使用,實現更多樣化的結果。

案例:

假設情況下,男70歲退休,女65歲退休(政策有變,先不予考慮)。我們可以根據員工的出生日期和性別來確定退休日期。

D2儲存格公式「=DATE(YEAR(C2)+(B2="")*5+65,MONTH(C2),DAY(C2))」。

2DATEDIF函數

DATEDIF函數是Excel的一個隱藏函數,不在插入函數的函式程式庫裡,在Excel輔助檔也沒有它的身影,通常情況下都是直接輸入,是非常重要的日期函數。

基本用法:

現有兩個日期,通過DATEDIF函數計算兩個日期之間的天數、月數和年數,計算結果及結果說明如下。

我可以用DATEDIF函數給即將結婚的玲玲寫了一封信:

親愛的玲玲:

今天是=TODAY()=2018824日,距離我們上一次相見(201261日)已經過去=DATEDIF(開始日期,今天的日期,"Y")=6年了,相當於=DATEDIF(開始日期,今天的日期,"M")=74月了,當然代表我們已經有=DATEDIF(開始日期,今天的日期,"D")=2275天沒有相見了,真心祝福你在人生剩下的=DATEDIF(今天的日期,100歲的日期,"D")=25567天裡一切順心,幸福美滿!

四、時間的計算

計算時間間隔的小時、分鐘和秒

間隔的小時=(結束時間-開始時間)*24

間隔的分鐘=(結束時間-開始時間)*24*60

間隔的秒=(結束時間-開始時間)*24*60*60

 

相關主題:

DATEDIF

055年資(齡)計算

338DATEDIF函數的語法及實際應用案例

226使用Excel函數計算年齡(年資)的三種方法

379職場人士(台幹),這些Excel函數公式你都要會

399用DATEDIF函數輕鬆搞定各類到期提醒及工齡計算

515DATEDIF函數的使用方法


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()