Calculate difference between two dates,but not weekends

  • Thread starter Thread starter LyonS
  • Start date Start date
L

LyonS

Hi, can someone help. I want to calculate the difference between two dates,
but not count the days if the difference included a Saturday or Sunday.

E.G. Monday, July 21,2008 - Friday, July 20,2008 = 3 days, however, I want
it to return a value of 1

Thank You, LyonS
 
I believe you'll need to create your own version of the DateDiff function.
For instance, something like this (typed on the fly without any testing):

Public Function DateDiffWeekDay(ByVal prmFirstDate As Date, ByVal
prmLastDate As Date) As Integer

Dim dtmDate As Date
Dim intDays As Integer

If prmFirstDate = prmLastDate Then
intDays=0
ElseIf prmFirstDate<prmLastDate Then
dtmDate = prmLastDate
Do While dtmDate >= prmFirstDate
if Format(dtmDate,"ddd") <> "Sat" and Format(dtmDate,"ddd")<>"Sun"
Then
intDays=intDays + 1
End If
dtmDate = DateAdd("d",-1,dtmDate)
Loop
Else
...Do the same thing in reverse

End If

DateDiffWeekDay = intDays

End Function

Hope that helps.

Steve
 
'============================================================
' function by Klatuu
' Weekdays (no adjustment for holidays)
'============================================================
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
end function

'===========================================================
' Doug Steele
' Weekdays (no adjustment for holidays)
'===========================================================
SQL Statement:
'Number of weekdays between two dates, by Doug Steele MVP

SELECT DateDiff("d", dte1, dte2) -
DateDiff("ww", dte1, dte2, 1) * 2 -
IIf(Weekday(dte2, 1) = 7,
IIf(Weekday(dte1, 1) = 7, 0, 1),
IIf(Weekday(dte1, 1) = 7, -1, 0)) As WeekdaysBetween2Dates
FROM tblMyTable


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you for responding. This is what I ended up doing and it worked.

weekdays: (((DateDiff("d",[pull date],[eff date])-(DateDiff("ww",[pull
date],[eff date],7)+DateDiff("ww",[pull date],[eff date],1)))))
 
Back
Top