DateDiff excluding weekends - one case not working

  • Thread starter Thread starter Cristen
  • Start date Start date
C

Cristen

Hello. I am using the following code to get the difference in minutes
between two dates, excluding weekends. It's working for all scenarios except
for when the end date is on a Saturday or Sunday. When that occurs I want it
to just calculate the difference between the two dates without any
modifications. Right now for those cases I am getting negative numbers. I
am not sure how to modify this code further to allow for that. I used this
code I found on another posting, so I'm not very advanced at this. Thank you
very much!

..iMin = Format(DateDiff("n", sPrevDate, sAppDate) - _
DateDiff("ww", sPrevDate, sAppDate, vbSunday) * 2880 - _
IIf(Weekday(sAppDate, vbSunday) = vbSaturday, _
IIf(Weekday(sPrevDate, vbSunday) = vbSaturday, 0, 1440), _
IIf(Weekday(sPrevDate, vbSunday) = vbSaturday, -1440, 0)), "###0.00")
 
' A place to start.

Option Explicit
Option Compare Text


Sub TestIt()
Dim StartDate As Date
Dim EndDate As Date

' The word Between might have different meanings.
Const conNeither As String = "Neither"
Const conNotFirstButLast As String = "NotFirstButLast"
Const conFirstButNotLast As String = "FirstButNotLast"
Const conBoth As String = "Both"

StartDate = Now()
EndDate = Now() + 1

MsgBox TotalMinutes(StartDate, EndDate, conFirstButNotLast)

End Sub


Public Function TotalMinutes(ByVal datStart As Date, _
ByVal dateEnd As Date, _
ByVal strBetween As String) As Long
Dim lngDay As Long
Dim lngTotal As Long

Select Case strBetween
Case "Neither"
datStart = datStart + 1
dateEnd = dateEnd - 1

Case "NotFirstButLast"
datStart = datStart + 1

Case "FirstButNotLast"
dateEnd = dateEnd - 1

Case "Both"
' Use both arguments as passed.

End Select

' Remove fractional days.
For lngDay = Int(datStart) To Int(dateEnd)
If Weekday(lngDay, vbSunday) <> vbSunday And _
Weekday(lngDay, vbSunday) <> vbSaturday Then
lngTotal = lngTotal + 1440
End If
Next lngDay

TotalMinutes = lngTotal

End Function
 
Back
Top