Calculating dates leaving Sundays out.

  • Thread starter Thread starter Floyd Forbes
  • Start date Start date
F

Floyd Forbes

How do I calculate days of the week while excluding Sundays.
I don't want Sundays included in the number of days.

Floyd
 
You could write some simple VB similar to below:

Public Function CalcDaysBetween(dteStartDate As Date,
dteEndDate As Date, booIncludeSunday As Boolean) As Long

Dim lngTotalDays As Long
Dim i As Integer

lngTotalDays = DateDiff("d", dteStartDate, dteEndDate)

If booIncludeSunday = True Then
CalcDaysBetween = lngTotalDays
Exit Function
Else
For i = 1 To lngTotalDays
If Weekday(dteStartDate) <> vbSunday Then
CalcDaysBetween = CalcDaysBetween + 1
End If
Next i
End If


End Function




-----Original Message-----
How do I calculate days of the week while excluding
Click to expand...
Sundays.
 
Oops! I forgot to increment the date. In the revised
code below, I'v also added a decode for the weekday and a
debug line so you can see the progression.

Public Function CalcDaysBetween(dteStartDate As Date,
dteEndDate As Date, booIncludeSunday As Boolean) As Long

Dim lngTotalDays As Long
Dim dteRunningDate As Date
Dim i As Integer

lngTotalDays = DateDiff("d", dteStartDate, dteEndDate)

dteRunningDate = dteStartDate

If booIncludeSunday = True Then
CalcDaysBetween = lngTotalDays
Exit Function
Else
For i = 1 To lngTotalDays
Debug.Print dteRunningDate & ":" &
DecodeWeekday(Weekday(dteRunningDate)) & ":" & IIf(Weekday
(dteRunningDate) = vbSunday, "Excluded", "Included") & ":"
& CalcDaysBetween
If Weekday(dteRunningDate) <> vbSunday Then
CalcDaysBetween = CalcDaysBetween + 1
End If
dteRunningDate = DateAdd("d", dteRunningDate,
1)
Next i
End If

End Function

Public Function DecodeWeekday(intCode) As String

Select Case intCode
Case 1
DecodeWeekday = "Sunday"
Case 2
DecodeWeekday = "Monday"
Case 3
DecodeWeekday = "Tuesday"
Case 4
DecodeWeekday = "Wednesday"
Case 5
DecodeWeekday = "Thursday"
Case 6
DecodeWeekday = "Friday"
Case 7
DecodeWeekday = "Saturday"
Case Else
DecodeWeekday = "Unknown"
End Select

End Function
 
Back
Top