投稿‎ > ‎

VBAで次の営業日を求める

posted Dec 15, 2014, 8:35 PM by Zhang Wenxu
次の営業日：
Public Function getNextWorkday(begin As Date) As Date
  Dim nextDate As Date
  Dim i As Integer

  begin = Format(begin, "yyyy/mm/dd")
  For i = 1 To 10
    nextDate = DateAdd("d", i, begin)
    If Application.WorksheetFunction.NetWorkDays(begin, nextDate, Range("祭日")) _
        Application.WorksheetFunction.NetWorkDays(begin, begin, Range("祭日")) = 1 Then
             getNextWorkday = nextDate
             Exit Function
     End If
   Next
End Function

前の営業日：
Public Function getPrevWorkday(endDate As Date) As Date
  Dim prevDate As Date
  Dim i As Integer

  endDate = Format(endDate, "yyyy/mm/dd")
  For i = -1 To -10
    prevDate = DateAdd("d", i, endDate)
    If Application.WorksheetFunction.NetWorkDays(prevDate, endDate, Range("祭日")) _
        Application.WorksheetFunction.NetWorkDays(endDate, endDate, Range("祭日")) = 1 Then
             getPrevWorkday = prevDate
             Exit Function
     End If
   Next
End Function
※祭日は、土日以外の祭日のリストである。

          
Comments