網友問到 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)<TIME(1,30,0)

因為加班超過30分鐘才起算加班。

TIME(1,30,0):找出1.5小時代表的數值。

D2-TIME(17,30,0)<TIME(1,30,0) 用以判斷儲存格D2的時間是否已加班超過30分鐘,並且在二小時以下。

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。

arrow
arrow
    文章標籤
    Excel TIME WEEKDAY
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()