EXCEL日期及時間函數(shù)看這一篇就夠——25個綜合實例應(yīng)用

L羅樂 2018-02-19

展開全文

1、轉(zhuǎn)換字符串日期

如下圖，將A列字符串日期轉(zhuǎn)換成標(biāo)準(zhǔn)日期格式；

D2單元格公式=--TEXT(A2,''0-00-00'')

2、日期轉(zhuǎn)換為文本字符串

如下圖，將A2單元格標(biāo)準(zhǔn)日期轉(zhuǎn)換成文本字符串；

B2單元格公式=TEXT(A2,''yyyymmdd'')

C2單元格公式=TEXT(A2,''yyyymd'')

3、生成系統(tǒng)當(dāng)前日期及時間

=TODAY()，生成系統(tǒng)當(dāng)前的日期

=NOW()，生成系統(tǒng)當(dāng)前的日期他時間

4、記錄當(dāng)前日期及時間且不再發(fā)生變化

B列設(shè)置為數(shù)據(jù)有效性，選擇F1單元格；

F1單元格輸入公式=NOW()

5、計算兩個日期間的天數(shù)及根據(jù)日期和天數(shù)計算日期

日期之間是可以直接相加減的，系統(tǒng)默認(rèn)日期1900日期系統(tǒng)，1900年1月1日日期序列值為1。

A2單元格2018/1/1序列值就是43101，2018/2/5序列值是43136，43136-43101相減得出結(jié)果為35天。

6、計算本月第一天及最后一天日期

如下圖計算本月第一天及最后一天，B2單元格公式=EOMONTH(A2,-1) 1，-1表示上個月最后一天， 1即可得到次月（即本月）第一天日期，C2單元格公式=EOMONTH(A2,0)，第二參數(shù)為0表示當(dāng)月最后一天。具體用法請點擊Excel日期及時間函數(shù)——EOMONTH函數(shù)

7、計算3個月后最后一天及3個月前最后一天日期

3個月后最后一天B2單元格公式=EOMONTH(A2,3)，3個月前最后一天日期C2單元格公式=EOMONTH(A2,-3)，正值生成未來日期，負(fù)值生成過去日期

8、計算3個月后日期及3個月前日期

3個月后日期B2單元格公式=EDATE(A2,3)，3個月前日期C2單元格公式=EDATE(A2,-3)，正值生成未來日期，負(fù)值生成過去日期，具體用法請點擊Excel日期及時間函數(shù)——EDATE函數(shù)

9、根據(jù)指定日期匯總銷售額

如下圖根據(jù)業(yè)務(wù)日期統(tǒng)計1-6月份的銷售金額，D2單元格輸入公式=SUMPRODUCT((MONTH(A2:A13)<7)*B2:B13),先用MONTH函數(shù)計算月份是否小于7，在用SUMPRODUCT函數(shù)返回乘積之和即為1-6月的銷售金額

10、判斷指定日期判斷所在的季度

先用MONTH函數(shù)計算月份，再用2的乘冪得出1-4位數(shù)數(shù)字，最后用LEN函數(shù)取數(shù)字的長度即為所在季度。

11、判斷指定日期是否為閏年

B2單元格公式=IF(MONTH(DATE(YEAR(A2),2,29))=2,''閏年'',''平年'')，先用YEAR函數(shù)計算年份，然后用DATE函數(shù)計算該年度是否額2月29日，如果沒有DATE函數(shù)將返回3月1日的序列值，再用MONTH函數(shù)判斷日期是否為2月，就則為閏年

12、根據(jù)日期計算合同到期日

C2單元格公式=EDATE(A2,B2)

反過來知道簽訂日期及到期日，要計算合同期限為多少月應(yīng)該怎么做了，直接相減在除以30天，最后用INT函數(shù)取整就得到天數(shù)

13、根據(jù)出生日期計算退休日期

如下圖根據(jù)出生日期計算退休日期，根據(jù)規(guī)定，男性退休時間為60歲，女性為55歲，換算成月份結(jié)果為720、660。

C2單元格公式=EDATE(A2,IF(B2=''男'',720,660))

14、根據(jù)日期計算本月、本年天數(shù)及剩余天數(shù)

如下圖計算本月、本年天數(shù)及剩余天數(shù)，由于每月月末最后一天日期就是當(dāng)月的總天數(shù)，用EOMONTH函數(shù)即可計算當(dāng)月最后一天的日期，再用DAY函數(shù)取天數(shù)即可。用EOMONTH函數(shù)的結(jié)果減掉TODAY函數(shù)的結(jié)果就是剩余天數(shù)，相當(dāng)于兩個日期相減，（2018/2/28序列值為43159；2018/2/9序列值為43140）

15、根據(jù)入職日期計算員工工齡費

如下圖根據(jù)員工入職日期計算2018年1月工齡費用，入職滿一年工齡費100，最高1000封頂，開始時間即入職時間，結(jié)束時間即2018/1/1。

C2單元格公式為=MIN(1000,DATEDIF(B2,''2018/1/1'',''Y'')*100)

16、根據(jù)日期計算賬齡

如下圖根據(jù)應(yīng)收賬款業(yè)務(wù)的發(fā)生日期計算賬齡，D2單元格公式=LOOKUP(DATEDIF(B2,TODAY(),''M''),{0,12,24},{''1年以內(nèi)'',''1-2年'',''2年以上''})

先用DATEDIF函數(shù)計算B列日期與當(dāng)前日期間隔的整月數(shù)，再用LOOKUP函數(shù)判斷DATEDIF函數(shù)結(jié)果在數(shù)組{0,12,24}對應(yīng)的值{''1年以內(nèi)'',''1-2年'',''2年以上''}

17、根據(jù)日期精確計算員工工齡

如下圖根據(jù)入職日期按年、月、日精確計算員工工齡，選中C2:E8區(qū)域，輸入下列公式按【三鍵】結(jié)束

=DATEDIF(B2:B8,TODAY(),{''y'',''ym'',''md''})

Y代表年，YM代表忽略日和年的月數(shù)差，MD代表忽略月和年的天數(shù)差

具體用法請點擊Excel日期及時間函數(shù)——DATEDIF函數(shù)、Excel日期及時間函數(shù)——DATEDIF函數(shù)設(shè)置生日提醒

18、根據(jù)日期計算是星期幾

WEEKDAY函數(shù)第二參數(shù)使用2表示數(shù)字1（星期一）到7（星期日）

19、計算每個月工資發(fā)放時間

如下圖計算2018年每月發(fā)工資時間，每個月28號發(fā)工資，如遇星期六星期日則提前星期五發(fā)，B2單元格輸入公式

=DATE(2018,A2,28)-TEXT(WEEKDAY(DATE(2018,A2,28),2)-5,''0;!0;!0'')

年份為2018固定，天數(shù)為28日固定，用DATE函數(shù)DATE(2018,A2,28)組成一個日期，在用WEEKDAY函數(shù)計算DATE結(jié)果為星期幾再減去5，如果是星期六結(jié)果為1，星期日結(jié)果為2，星期一到星期五為負(fù)數(shù)或0。TEXT函數(shù)用代碼''0;!0;!0''將結(jié)果為正數(shù)的顯示原值，其它強制顯示為0，最后用DATE(2018,A2,28)減去TEXT的結(jié)果得到實際工資發(fā)放日期

20、根據(jù)日期計算15個工作日后的日期

如下圖根據(jù)供應(yīng)商的付款日期，15個工作日內(nèi)開具發(fā)票并寄送，休息日及法定假日順延，C2單元格公式=WORKDAY(B2,15,F$2:F$8)

21、根據(jù)日期計算當(dāng)月應(yīng)出勤天數(shù)

如下動畫，根據(jù)年月日期根據(jù)單雙休情況計算每月應(yīng)出勤天數(shù)，雙休C5單元格公式=NETWORKDAYS(B$2&-D$2,EOMONTH(B$2&-D$2,0))，單休H5單元格公式=NETWORKDAYS(G$2&-I$2,EOMONTH(G$2&-I$2,0)) INT((WEEKDAY((G$2&-I$2)-6,2) EOMONTH(G$2&-I$2,0)-(G$2&-I$2))/7)

單休公式也可以使用=NETWORKDAYS.INTL(G$2&-I$2,EOMONTH(G$2&-I$2,0),11)，11表示星期日為休息。

22、根據(jù)日期時間計算小時及分鐘

如下圖根據(jù)開始及結(jié)束的日期及時間計算間隔小時、分鐘

D2單元格公式=TEXT(C2-B2,''[h]'')

E2單元格公式=TEXT(C2-B2,''[m]'')-D2*60

F2單元格公式=TEXT(C2-B2,''[m]'')

23、生成隨機時間分鐘及秒數(shù)

以分鐘為單位的隨機時間公式=RANDBETWEEN(0,1440)/1440

1440為一天的分鐘數(shù)，24*60

以秒數(shù)為單位的隨機時間公式=RANDBETWEEN(0,86400)/86400

86400為一天的秒數(shù)，24*60*60

24、隨機生成日期及時間