Datediff not including weekday?????

[kevinjwilson@gmail.com]

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@FortuneJames.com]

Re: Datediff not including weekday?????

kevinjwilson@gm ail.com wrote:[color=blue]
> 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?[/color]

Here is what I use to count weekend days:

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

'This function assumes dtStart <= dtEnd
CountWeekendDay s = 0
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDay s = 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 (CountWeekendDa ys(#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
CDMAPoster@Fort uneJames.com

[kevinjwilson@gmail.com]

Re: Datediff not including weekday?????

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

CDMAPoster@Fort uneJames.com wrote:[color=blue]
> kevinjwilson@gm ail.com wrote:[color=green]
> > 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?[/color]
>
> Here is what I use to count weekend days:
>
> '---Begin Code
> Public Function CountWeekendDay s(dtStart As Date, dtEnd As Date) As
> Integer
> Dim intSat As Integer
> Dim intSun As Integer
>
> 'This function assumes dtStart <= dtEnd
> CountWeekendDay s = 0
> intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
> intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
> CountWeekendDay s = 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 (CountWeekendDa ys(#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
> CDMAPoster@Fort uneJames.com[/color]

[kevinjwilson@gmail.com]

Re: Datediff not including weekday?????

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))

kevinjwilson@gm ail.com wrote:[color=blue]
> 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
>
> CDMAPoster@Fort uneJames.com wrote:[color=green]
> > kevinjwilson@gm ail.com wrote:[color=darkred]
> > > 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?[/color]
> >
> > Here is what I use to count weekend days:
> >
> > '---Begin Code
> > Public Function CountWeekendDay s(dtStart As Date, dtEnd As Date) As
> > Integer
> > Dim intSat As Integer
> > Dim intSun As Integer
> >
> > 'This function assumes dtStart <= dtEnd
> > CountWeekendDay s = 0
> > intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
> > intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
> > CountWeekendDay s = 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 (CountWeekendDa ys(#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
> > CDMAPoster@Fort uneJames.com[/color][/color]

[Ron2006]

Re: Datediff not including weekday?????

Similar to others, and does work in query

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

[CDMAPoster@FortuneJames.com]

Re: Datediff not including weekday?????

Ron2006 wrote:[color=blue]
> Similar to others, and does work in query
>
> workdays
> =DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate],7)+DateDiff("w w",[StartDate],[EndDate],1))
> + 1[/color]

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
CDMAPoster@Fort uneJames.com

[CDMAPoster@FortuneJames.com]

Re: Datediff not including weekday?????

kevinjwilson@gm ail.com wrote:[color=blue]
> 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[/color]

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

James A. Fortune
CDMAPoster@Fort uneJames.com