資料來源:Jamyy's Weblog

Excel VBA: 計算員工年資與年度特休天數

目的: 算出員工任職年、月、天數, 並依勞基法規定計算年度特休天數

Excel 工作表

分別輸入員工姓名、到職日期與年資基準日 (2007 年年假之年資基準日為 2006/12/31)

年資與年假為 VBA 程式運算結果

VBA 程式碼

Sub Main() Sheet1.Activate dateBase = Cells(1, "I") '年資基準日 For i = 2 To ActiveSheet.UsedRange.Rows.Count dateStaff = Cells(i, "B") '到職日 intFlag = IIf(Day(dateStaff) = 1, 1, 0) '若到職日為月份首日,於 DateDiff() 運算時累加一個月 intTotalMonth = DateDiff("m", dateStaff, dateBase) + intFlag Cells(i, "C") = Int(intTotalMonth / 12) & "年" Cells(i, "D") = (intTotalMonth Mod 12) & "月" Cells(i, "E") = IIf(intFlag, "0天", DateDiff("d", dateStaff, DateAdd("m", 1, dateStaff) - Day(dateStaff)) + 1 & "天") Cells(i, "F") = fn_Result(Int(intTotalMonth / 12)) Next i End Sub Function fn_Result(n) As String Select Case n Case Is < 1 strOutput = "0天" Case Is < 3 strOutput = "7天" Case Is < 5 strOutput = "10天" Case Is < 10 strOutput = "14天" Case Else strOutput = IIf((14 + n - 10) > 30, 30, (14 + n - 10)) & "天" End Select fn_Result = strOutput End Function

運算邏輯

‧變數與自定函數說明

dateBase = 年資基準日
dateStaff = 員工到職日期
intFlag = 用於 DateDiff() 運算時, 是否累加一個月
intTotalMonth = 員工任職總月數

fn_Result(n) = 代入員工任職年數, 傳回年假天數

‧員工年資年、月數之計算: 先將員工年資換算成總月數, 再分別算出年數與月數

intTotalMonth = DateDiff("m", dateStaff, dateBase) + intFlag
年數 = Int(intTotalMonth / 12)
月數 = intTotalMonth Mod 12

ps. 若到職日為月份首日, 於 DateDiff() 運算時累加一個月 (intFlag = 1)

‧年資天數為 "到職日至當月月底" 之天數

天數 = DateDiff("d", dateStaff, DateAdd("m", 1, dateStaff) - Day(dateStaff)) + 1

'假設到職日為 1988 年 9 月 10 日
dateStaff = "1988/9/10"
DateAdd("m", 1, dateStaff) = "1988/10/10"
DateAdd("m", 1, dateStaff) - Day(dateStaff) = "1988/9/30"
DateDiff("d", dateStaff, DateAdd("m", 1, dateStaff) - Day(dateStaff)) + 1 = 21
'該月份任職天數為 21 天

ps. 若於前一步驟 (計算年、月) 已累加一個月, 則天數應歸 0 (因已進位)

‧年度特休天數依勞基法第三十八條 (特別休假) 規定

服務滿一年以上，未滿三年者，每年給假七日。
服務滿三年以上，未滿五年者，每年給假十日。
服務滿五年以上，未滿十年者，每年給假十四日。
服務滿十年以上，每滿一年，加給一日，最多以三十日為限。