工作时间

描述

在本文中，我们将考虑如何计算两个日期之间的工作时间。在计件工资制度的情况下，分析员工的工作效率和计算所花费的有用时间可能都需要该信息。

因此，让我们编写自己的函数来帮助我们计算工作时间，并将其命名为WORKHOURS。

打开菜单 服务-宏-编辑器Visual Basic , 插入新模块 （菜单 Insert - Module ）并将该函数的文本复制到此处：

Function WORKHOURS(StartDate As Variant, EndDate As Variant)
    Application.Volatile
    StartDate = StartDate.Address
    EndDate   = EndDate.Address
    
    ' 第一天工作时间 
    WORKHOURS1 = Evaluate("IF(AND(DAY(" & StartDate & ")=DAY(" & _
    EndDate & "),MONTH(" & StartDate & ")=MONTH(" & EndDate & "))," & EndDate & _
    "-" & StartDate & "-IF(AND(HOUR(" & StartDate & ")<=13,HOUR(" & EndDate & _
    ")>=14),1/24,0),18/24-MOD(" & StartDate & ",1)-IF(HOUR(" & StartDate & _
    ")<=13,1/24,0))")
    
    ' 中间日工作时间 
    WORKHOURS2 = Evaluate("MAX((IF(AND((WEEKDAY(" & EndDate & ",2))"&"<"&"(WEEKDAY(" & StartDate & _
    ",2)),((WEEKDAY(" & StartDate & ",2))-(WEEKDAY(" & EndDate & ",2)))>1),(((DATEDIF(" & StartDate & _
    "," & EndDate & "," & """D""" & ")+1))-(FLOOR((DATEDIF(" & StartDate & "," & EndDate & _
    "," & """D""" & ")+1)/7,1)*2)-2),(((DATEDIF(" & StartDate & "," & EndDate & "," & """D""" & ")+1))-(FLOOR((DATEDIF(" & _
    StartDate & "," & EndDate & "," & """D""" & ")+1)/7,1)*2)))-2),0)*8/24")
    
    ' 最后一天的工作时间 
    WORKHOURS3 = Evaluate("IF(AND(DAY(" & StartDate & _
    ")=DAY(" & EndDate & "),MONTH(" & StartDate & ")=MONTH(" & EndDate & ")),0,MOD(" & EndDate & _
    ",1)-9/24-IF(HOUR(" & EndDate & ")>=14,1/24,0))")
    
    WORKHOURS = WORKHOURS1 + WORKHOURS2 + WORKHOURS3
End Function

关闭 Visual Basic 编辑器并返回到 Excel。

现在在 功能大师 在类别中 用户自定义 您可以找到我们的 WORKHOURS 函数并使用它。函数语法 下列的：

 =WORKHOURS(开始日期;结束日期)