如何計(jì)算兩個(gè)日期間的工作日天數(shù)?超實(shí)用的5類日期函數(shù)來了!
?
作者:老菜鳥來源:部落窩教育發(fā)布時(shí)間:2022-02-21 14:13:35點(diǎn)擊:7335
編按:
在Excel中與日期有關(guān)的公式很常見,涉及到賬戶應(yīng)付款處理、項(xiàng)目日程安排、時(shí)間管理等很多方面,今天給大家分享幾個(gè)與星期、工作日等相關(guān)的公式,例如計(jì)算兩個(gè)日期間的工作日數(shù)、判斷一個(gè)日期是否為周末等。小伙伴們,趕緊來看一看吧!
下面,就讓我們通過5個(gè)案例來一一進(jìn)行介紹。
案例1:如何判斷某個(gè)日期是否為周末?
人事部需要對(duì)1月份有加班記錄的日期判斷是周末還是工作日,效果如圖所示。
為了方便大家驗(yàn)證結(jié)果,用=TEXT(B2,"AAAA")公式備注了一下。
示例中判斷周末的公式為:=IF(WEEKDAY(B2,2)>5,"周末","工作日")
公式解析:這個(gè)公式中,WEEKDAY函數(shù)的第2參數(shù)為2,得到的就是B列日期的星期數(shù)字,6代表星期六、7代表星期日,因此只需判斷 WEEKDAY 函數(shù)的返回值是否大于 5,即可判斷出對(duì)應(yīng)日期是否為周末。
案例2:如何計(jì)算離職員工最后一個(gè)月的實(shí)際工作天數(shù)?
=MAX(NETWORKDAYS(EOMONTH(B2,-1)+1,B2)-IFERROR(VLOOKUP(C2,$F$2:$H$8,3,0),),)
公式解析:
① EOMONTH(B2,-1)+1得到的是離職月的1號(hào);
② NETWORKDAYS函數(shù)得到離職月1號(hào)至離職日期之間的工作日天數(shù)(不含周六和周日);
③ VLOOKUP(C2,$F$2:$H$8,3,0)的作用是匹配出離職月的法定節(jié)假日天數(shù);
④ 使用IFERROR函數(shù)將沒有法定節(jié)假日的月份返回0,否則會(huì)得到錯(cuò)誤值無法計(jì)算;
⑤ 工作日天數(shù)-法定節(jié)假日是最終的上班天數(shù),加上一個(gè)MAX防止出現(xiàn)負(fù)數(shù)。
這個(gè)公式比較復(fù)雜,有需要的話套用即可。
案例3:如何計(jì)算兩個(gè)日期間星期日的天數(shù)?
某公司要給員工在一段時(shí)間內(nèi)周日上班的補(bǔ)發(fā)津貼,因此需要統(tǒng)計(jì)出周日的天數(shù),結(jié)果如圖所示。
NETWORKDAYS.INTL函數(shù)是Excel 2010版才有的函數(shù),特點(diǎn)是第三參數(shù)可以使用代碼來指定需要統(tǒng)計(jì)周幾的天數(shù)。
例如“1111110”這種格式中,1表示休息,0表示上班,0在第七位,表示周日上班。函數(shù)統(tǒng)計(jì)兩個(gè)日期之間的上班天數(shù),也就是周日的天數(shù)。
如果要統(tǒng)計(jì)兩個(gè)日期之間周二的天數(shù),只需要將第二位用0表示即可,代碼為“1011111”。
案例4:根據(jù)訂貨日期推算交貨日期?
已知訂貨日期和交貨期限(工作日),可以使用=WORKDAY(B2,C2)來推算出具體的交貨日期。
注意,這個(gè)示例中的交貨期限是指不含周六周日的天數(shù),所以不能直接用訂貨日期+交貨天數(shù)來計(jì)算。
WORKDAY函數(shù)就是根據(jù)指定日期來推算出若干個(gè)工作日之后的日期,格式為:WORKDAY(開始日期,天數(shù))。
案例5:推算母親節(jié)的具體日期?
有些特殊的節(jié)日,比如母親節(jié)、父親節(jié)、感恩節(jié)等,其節(jié)日是根據(jù)特定月中的第幾個(gè)星期幾來定義的,如果要確定其日期就變得比較棘手。
例如母親節(jié)是每年5月的第2個(gè)星期日,知道年份的話就可以用公式=CEILING(DATE(A2,4,30),7)+8來推算出該年的母親節(jié)的具體日期。
公式解析:
① DATE(A2,4,30) 是指定年份4月30日的日期;
② 利用CEILING函數(shù)將日期向上舍入到最接近的7的倍數(shù)(在Excel中,7的倍數(shù)所代表的日期序列正好都是星期六);
③ 得出5月份第一個(gè)星期六的日期,再加8,就是5月份第2個(gè)星期日即“母親節(jié)”的日期。
這個(gè)公式利用了1900日期系統(tǒng)的特性,如果不明白的話套用公式即可。
今天分享的這五個(gè)例子,在日期類的問題中算是比較有難度的,涉及的函數(shù)大家平時(shí)也用的不多,建議收藏起來以備不時(shí)之需。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù),非得用VBA才能實(shí)現(xiàn)嗎?
Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能!
版權(quán)申明:
本文作者老菜鳥;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!