Datediff not including weekday?????

[kevinjwilson]

I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

[CDMAPoster]

ke**********@gmail.com wrote:

I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

[kevinjwilson]

I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

CD********@FortuneJames.com wrote:

ke**********@gmail.com wrote:

I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

[kevinjwilson]

I found a solution that looks like it works in query functions

DateDiff('d',[test]![date1],[test]![date2],2)-(IIf(DateDiff('ww',[test]![date1],[test]![date2],2)=0,DateDiff('ww',[test]![date1],[test]![date2],2),(DateDiff('ww',[test]![date1],[test]![date2],2))*2))

ke**********@gmail.com wrote:

I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

CD********@FortuneJames.com wrote:

ke**********@gmail.com wrote:

I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?

Here is what I use to count weekend days:

'---Begin Code
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As
Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'---End Code

Sample Call:
MsgBox (CountWeekendDays(#3/1/06#, #5/1/06#))

18

Use the same dates you are using for your DateDiff in this function and
subtract the result from your DateDiff result.

James A. Fortune
CD********@FortuneJames.com

[Ron2006]

Similar to others, and does work in query

workdays
=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1))
+ 1

[CDMAPoster]

Ron2006 wrote:

Similar to others, and does work in query

workdays
=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1))
+ 1

I have read here that using ww for some versions of Access doesn't
return the correct value for some dates. That's why I didn't use it.
Maybe someone can clarify which situations may be dangerous..

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

ke**********@gmail.com wrote:

I am sure that works well in forms or macros, but will it work as an
expression in a query?

I should have noted, I am trying to do this through a query.

Sorry

If the code is placed in a module rather than behind a form then you
can call it from a query.

James A. Fortune
CD********@FortuneJames.com