Datediff calculation error

[PPCLI]

Can anyone suggest a work around on this problem. It seems that formula for
date difference, weekdays does not work in Access 2003. The formula to
calculate the date difference (# of weekdays) between 2 dates gives the
week's difference. The formula is:

=datediff("W",[first date],[second date])

I've seen on other message boards that there is a program error in this
version of Access but it seems that Microsoft doesn't want to address it.

Any help would be appreciated.

 

[PPCLI]

What? I know for a fact the dates I'm using are weekdays. I don't understand
your explaination. If I wanted # of weeks then yes I'd use the "WW" format. I
want the # of weekdays "W". Whats the solution?

Check the help-file. You're misinterpreting what datediff("w"... is going to
return.

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. If interval is Week ("ww")
, however, the DateDiff function returns the number of calendar weeks between
the two dates. It counts the number of Sundays between date1 and date2.
DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1,
even if it does fall on a Sunday.

Bob

Can anyone suggest a work around on this problem. It seems that formula for
date difference, weekdays does not work in Access 2003. The formula to
calculate the date difference (# of weekdays) between 2 dates gives the
week's difference. The formula is:

=datediff("W",[first date],[second date])

I've seen on other message boards that there is a program error in this
version of Access but it seems that Microsoft doesn't want to address it.

Any help would be appreciated.

 

[PPCLI]

So the formula is misrepresented. It should be the weeks difference if the
first date falls on a monday. What the hell use is this? So to get the number
of weekdays I have to use a very complicated formula that your presented? I
think I'll just have to do the weekday calculation in Excel where it works
with no issues.

DateDiff("W",[StartDate],[EndDate])

Try this:

Function FindWorkDays2(varDate1 As Variant, varDate2 As Variant, Optional
Incl1) As Double
'*******************************************
'Purpose: returns number of workdays between two dates
' based on 5-day work week (Monday - Friday)
'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

Incl1 = IIf(IsMissing(Incl1), True, Incl1)

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 - x '# 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
FindWorkDays2 = fdays + fweeks + ldays + IIf(Incl1, 0, IIf
(IsWeekday(dteDate1), -1, 0))
End If
End If

End Function

Bob

Check the help-file. You're misinterpreting what datediff("w"... is going to
return.

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. If interval is Week ("ww")
, however, the DateDiff function returns the number of calendar weeks between
the two dates. It counts the number of Sundays between date1 and date2.
DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1,
even if it does fall on a Sunday.

Bob

Can anyone suggest a work around on this problem. It seems that formula for
date difference, weekdays does not work in Access 2003. The formula to

[quoted text clipped - 7 lines]

Any help would be appreciated.