2017年6月16日 星期五

Excel-判斷加班時數(TIME,WEEKDAY)

網友問到 Excel 的問題:公司上班時間為08:00-17:00,下班超過30分鐘開始算加班(如下圖),加班前二小時算加班1,超過二個小時算加班2,如何標記假日加班,該如何處理?
Excel-判斷加班時數(TIME,WEEKDAY)

【公式設計與解析】
1. 加班1
儲存格E2:=IF((D2<>"")*(D2>=TIME(17,30,0)),IF(D2-TIME(17,30,0)<
TIME(1,30,0),"V",""),"")
TIME(17,30,0):利用 TIME 函數找出17:30代表的數值。
(1) (D2<>"")*(D2>=TIME(17,30,0)
用以判斷二個條件是否同時成立。
條件一:儲存格D2為空白。
條件二:儲存格D2的時間大於17:30。
(2) D2-TIME(17,30,0)
因為加班超過30分鐘才起算加班。
TIME(1,30,0):找出1.5小時代表的數值。
D2-TIME(17,30,0)
2. 加班2
儲存格F2:=IF((D2<>"")*(D2>=TIME(17,30,0)),IF(D2-TIME(17,30,0)>=
TIME(1,30,0),"V",""),"")
D2-TIME(17,30,0)>=TIME(1,30,0)用以判斷儲存格D2的時間是否已加班超過30分鐘,並且在二小時以上。
3. 假日加班
儲存格G2:=IF((COUNTIF(E2:F2,"V")>0)*WEEKDAY(A2,2)>5,"V","")
使用雙條件來判斷是否為假日並且有加班。
條件一:COUNTIF(E2:F2,"V")>0,判斷在儲存格E2和儲存格F2中的『V』數量是否大於0。(若是,表示有一個『V』)
條件二:WEEKDAY(A2,2)>5,其中 WEEKDAY 函數中使用參數『2』,代表數字1~7對應星期一~星期日。當傳回值大於5時表示為星期六、日,即為假日。
image
複製儲存格E2:G2,貼至儲存格E2:G31。

沒有留言:

張貼留言

好康東東