贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到 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。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 訪客
  • 您好! 想請問下如果條件是"加班前二小時算加班1,超過二個小時算加班2"
    為何判斷是"TIME(1,30,0):找出1.5小時代表的數值", 而不是"TIME(2,0,0)"?
    單純在學習的時候有點霧煞煞, 故詢問下, 再次感謝您這麼豐富的教學~~ 受益良多!
  • 因為公式是和TIME(17,30,0)比較,當17:30之後1.5小時,就是前二小時。

    vincent 於 2017/06/17 17:14 回覆

  • Dora LW
  • 大神,為工作方便,我經常在你這尋找excel公式解決問題,但最近遇到一問題,想跟你請教下:

    公司上班時間為9:00-18:00

    18:30或之前下班不算是加班
    18:31之後下班才算加班
    加班超過15分鐘當半小時計算

    如:18:30或之前下班不算加班
    18:31下班算加班30分鐘
    18:41下班算加班30分鐘
    18:46下班算加班1小時
    19:14下班算加班1小時

    并且有些同事會熬通宵

    以此類推,一直做不出合適公式,望大神能幚忙下,萬分感謝,勿能以身相許 :p

    如可以的話,可以回覆我email:gl_3344outlook .com
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼