DateDiff Issue.. Any Thoughts

[rpferguson]

I am trying to calculate the number of weekdays between two dates in an access query.

I'm using Expr 1: DateDiff("w", #4/01/2006#, #02/26/2007#)

When I use this expression with "d" instead of "w" I get 331 days, but when I use "w" I get 47 days. I know this can't be right.... Is there something I'm missing here?

I looked up on msdn and "w" is for weekday. Is there something that I need to do to be more specific?

Any help is appreciated.


Thanks

 

[stopher]

When I use this expression with "d" instead of "w" I get 331 days, but when I use "w" I get 47 days.

You mean when you use "w" you get 47 weeks ("w" counts number of weeks). 331 days is 47 weeks and 2 days so it looks right to me.

Stopher

 

[raskew]

Hi -
DateDiff("d",#4/01/2006#, #02/26/2007#) returns the number of calendar days between the two dates.

Both DateDiff("w", #4/01/2006#, #02/26/2007#) and DateDiff("ww", #4/01/2006#, #02/26/2007#) return the number of full (7 day) calendar weeks between the dates, with a minor variation. See the Help file for a detailed explanation.

I'm assuming that when you specify weekdays you mean Monday - Friday. The following will bring you very close:

? int(5*(datediff("d",#4/01/2006#, #02/26/2007#)/7))
 236

To be completely accurate, it becomes a little more complex. Try copying/pasting this into a new module then, from the debug (immediate)window, enter:
? findworkdays(#4/01/2006#, #02/26/2007#)

Function FindWorkDays(varDate1 As Variant, varDate2 As Variant, Optional incl As Boolean) As Double

'*******************************************
'Purpose:   returns number of workdays between two dates
'Coded by:  raskew
'Inputs:    from debug (immediate) window
'           ? findworkdays(#4/01/2006#, #02/26/2007#)
'Outputs:   236
'*******************************************
Dim dteDate1 As Date
Dim dteDate2 As Date
Dim fdays    As Integer
Dim fweeks   As Integer
Dim ldays    As Integer
Dim intdays  As Integer
Dim x        As Integer

    incl = IIf(IsMissing(incl), True, incl)
    
    If Not IsNull(varDate1) And Not IsNull(varDate2) Then
       If IsDate(varDate1) And IsDate(varDate2) Then
          dteDate1 = DateValue(varDate1)
          dteDate2 = DateValue(varDate2)
          intdays = DateDiff("d", dteDate1, dteDate2)
          x = WeekDay(dteDate1, 1)
          
          fdays = 7 - WeekDay(dteDate1, 1) '# of weekdays in starting week
          fweeks = 5 * Int((intdays - fdays) / 7) '# of calendar days in full weeks
          ldays = WeekDay(dteDate2, 1) - 1 '#of weekdays in ending week
          FindWorkDays = fdays + fweeks + ldays + IIf(incl, 0, IIf(IsWeekday(dteDate1), -1, 0))
       End If
    End If

End Function

HTH - Bob

 

[Brianwarnock]

Just to clarify , from Access help

When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1.

Datediff must be the worst function devised by man

Brian

 

[stopher]

And Microsoft's explanation/resolution... here

Stopher