计算新发票日期,包括周末,不包括节假日

“假日”的定义因国家、公司、年份等不同而不同,因此没有适合您需要的标准功能。以下是我的首选方法的过度简化版本:

Function GetNetWorkDays(DateStart As Date, DateEnd As Date) As Integer
    Dim i As Date
    i = DateStart

    While i < DateEnd
        If i <> #01/01/1900# and _
           i <> #01/02/1900# Then _
            GetNetWorkDays = GetNetWorkDays + 1
        i = i + 1
    Wend
    Exit Function
End Function

其中01/01/1900和01/02/1900是您的假期选择。从长远来看,您需要将日期条件移动到它自己的表中。

我知道你想要一个有公式的解决方案,而不是VBA。

假设你有:

 A4:A8 -> your holidays table
 A14   -> your start date
 A16   -> number of days to add

生成以下辅助结构:

 c3    -> put a zero
 c4    -> =IF(AND(A4-A$14>0,(A4-A$14)<=(A$16+SUM(C$3:C3))),1,0)
 c5:c8 -> Copy down from c4

因此,要获取新日期,请在结果单元格中键入以下内容:

 =A14+A16+SUM(C4:C8)  

嗯!

谢谢你的回复。

最后我选择了:

Public Function GetCalendarDaysExHolidays(DateStart As Date, MaxDate As Date, NumDays As Integer) As Date
Dim i As Date
Dim DaysCounted As Integer
Dim Rng As Range

DaysCounted = 0

i = DateStart

Do While i < MaxDate

    Set Rng = Sheets("Bank holidays").Range("A1:A1000").Find(what:=Format(DateValue(i), "DD/MM/YYYY"), LookAt:=xlWhole, LookIn:=xlFormulas)
    If Rng Is Nothing Then
        DaysCounted = DaysCounted + 1
    End If

    i = i + 1

    If (DaysCounted = NumDays) Then
        GetCalendarDaysExHolidays = i
        Exit Do
    End If
Loop
Exit Function

结束函数