如何基于每月首个周一为工作日编号？附VBA首周一获取函数

需求说明 #

从每月首个周一开始为工作日连续编号，编号持续至次月首个周一，周末不计入编号（例：周五编号为5时，下周一编号为6）。基于已有的FirstMonday函数完成编号功能。

基础函数：获取每月首个周一 #

你提供的FirstMonday函数可正确返回指定日期所在月份的首个周一，代码如下：

Public Function FirstMonday(myDate As Date) As Date
    Dim d As Date, w As Long
    d = DateSerial(Year(myDate), Month(myDate), 1)
    w = Weekday(d, vbMonday)
    FirstMonday = d + IIf(w <> 1, 8 - w, 0)
End Function

实现工作日编号函数 #

以下提供两种方案，对应不同的跨月编号逻辑：

方案1：当月周期内编号（次月首个周一重置为1）

每个月的编号从当月首个周一开始，从1递增，周末跳过；次月首个周一作为新月份的编号起点，重置为1。

Public Function GetWorkdayID_Reset(targetDate As Date) As Variant
    ' 周末不计入编号，返回空值
    Dim dayOfWeek As Long
    dayOfWeek = Weekday(targetDate, vbMonday) ' 1=周一，5=周五，6=周六，7=周日
    If dayOfWeek >= 6 Then
        GetWorkdayID_Reset = Empty
        Exit Function
    End If
    
    ' 获取当月首个周一作为周期起始
    Dim cycleStart As Date
    cycleStart = FirstMonday(targetDate)
    
    ' 日期早于当月首个周一，返回空值
    If targetDate < cycleStart Then
        GetWorkdayID_Reset = Empty
        Exit Function
    End If
    
    ' 优先用Excel内置函数计算工作日数量，提高效率
    On Error Resume Next
    GetWorkdayID_Reset = WorksheetFunction.NetworkDays(cycleStart, targetDate)
    On Error GoTo 0
    
    ' 兼容无NetworkDays函数的环境，手动计算
    If IsEmpty(GetWorkdayID_Reset) Then
        Dim count As Long, currentDate As Date
        count = 0
        currentDate = cycleStart
        Do While currentDate <= targetDate
            If Weekday(currentDate, vbMonday) <= 5 Then
                count = count + 1
            End If
            currentDate = currentDate + 1
        Loop
        GetWorkdayID_Reset = count
    End If
End Function

方案2：跨月连续编号（次月首个周一延续上月编号）

编号从当月首个周一开始连续递增，周末跳过，直至次月首个周一（该周一的编号延续上月）；次月首个周一之后的工作日重置为1。

Public Function GetWorkdayID_Continue(targetDate As Date) As Variant
    ' 周末不计入编号，返回空值
    Dim dayOfWeek As Long
    dayOfWeek = Weekday(targetDate, vbMonday)
    If dayOfWeek >= 6 Then
        GetWorkdayID_Continue = Empty
        Exit Function
    End If
    
    Dim cycleStart As Date
    ' 判断目标日期是否为次月首个周一（前一天属于上月）
    If targetDate = FirstMonday(targetDate) And Month(targetDate - 1) <> Month(targetDate) Then
        ' 以上月首个周一作为周期起始
        cycleStart = FirstMonday(DateAdd("m", -1, targetDate))
    Else
        ' 以当月首个周一作为周期起始
        cycleStart = FirstMonday(targetDate)
    End If
    
    ' 计算工作日数量
    On Error Resume Next
    GetWorkdayID_Continue = WorksheetFunction.NetworkDays(cycleStart, targetDate)
    On Error GoTo 0
    
    ' 手动计算兼容
    If IsEmpty(GetWorkdayID_Continue) Then
        Dim count As Long, currentDate As Date
        count = 0
        currentDate = cycleStart
        Do While currentDate <= targetDate
            If Weekday(currentDate, vbMonday) <= 5 Then
                count = count + 1
            End If
            currentDate = currentDate + 1
        Loop
        GetWorkdayID_Continue = count
    End If
End Function

使用方法 #

在Excel单元格中直接调用函数即可：

• 方案1调用：=GetWorkdayID_Reset(A1)（A1为存放日期的单元格）
• 方案2调用：=GetWorkdayID_Continue(A1)

注意事项 #

• 两个函数均会对周末日期返回空值，符合“周末不计入编号”的要求
• NetworkDays函数依赖Excel对象库，若在非Excel环境使用，会自动切换为手动计算逻辑

内容的提问来源于stack exchange，提问作者Nata