DateDiff with "w" (weekday argument)

[kmacd6951]

I am wanting to calculate the difference between two dates.

I was originally using:
CRT: DateDiff("d",[SWB_SW_CASE]![SWDATECREATED],[SWDATERESOLVED])

but then I realized I was including weekends by doing this.

I tried:
CRT: DateDiff("w",[SWB_SW_CASE]![SWDATECREATED],[SWDATERESOLVED])

as helptext says that "w" is "Weekdays". This seems to be calculating my CRT based on weeks now, rather than days.

I also tried
CRT: DateDiff("w",[SWB_SW_CASE]![SWDATECREATED],[SWDATERESOLVED],2)
to tell it that my week starts on Monday, but no change.

So:
4/8/2004 9:00AM thru 4/8/2004 9:06AM should give me a CRT of 0

4/8/2004 9:00AM thru 4/12/2004 12:34AM should give me a CRT of 2

What am I missing?
Thanks
Katie

 

[mdmilner]

My suggestion is to follow this link and look at all the examples:

http://www.mvps.org/access/datetime/index.html

Just a thought but would the WEEKDAY function help you?
It returns the integer value of the day of the week.
' Weekday=1 Sunday -> 7 Saturday

Mike

 

[kmacd6951]

OK, so it looks like you do have to use code/module, eh?

Thanks!

 

[kmacd6951]

Still strugling a little...

Tried to use code from the website mentioned above by mdmilner:

'*********** Code Start **************
Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

	BegDate = DateValue(BegDate)

	EndDate = DateValue(EndDate)
      	WholeWeeks = DateDiff("w", BegDate, EndDate)
      	DateCnt = DateAdd("ww", WholeWeeks, BegDate)
      	EndDays = 0
      	Do While DateCnt < EndDate
        	If Format(DateCnt, "ddd") <> "Sun" And _
                          Format(DateCnt, "ddd") <> "Sat" Then
            		EndDays = EndDays + 1
         	End If
         	DateCnt = DateAdd("d", 1, DateCnt)
      	Loop
      	Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

When I put this into my query, it is telling me:
Undefined function 'WorkDays' in expression.

I called the Module WorkDays - was this incorrect?
My query has:
CRT: WorkDays([SWB_SW_CASE]![SWDATECREATED],[SWDATERESOLVED])


When I use the plain DateDiff, I like that is calcuates my CRT as "0" when the SWDATECREATED and SWDATERESOLVED are the same day. What I don't like is that it doesn't exclude weekends.

When I use a module for GetWorkDays (see below), it does NOT include weekends (good thing), but it gives me at CRT of "1" when the SWDATECREATED and SWDATERESOLVED are the same day.

Option Compare Database
Option Explicit


Public Function GetWorkDays(sStartDate, sEndDate) As Long
  Dim iDays As Long
  Dim iWorkDays As Long
  Dim sDay
  Dim i As Long
  
  iDays = DateDiff("d", sStartDate, sEndDate)
  
  iWorkDays = 0
  
  For i = 0 To iDays
    'First day of the week is sunday
    sDay = Weekday(DateAdd("d", i, sStartDate))
    If sDay <> 1 And sDay <> 7 Then
      iWorkDays = iWorkDays + 1
    End If
  Next
  GetWorkDays = iWorkDays
End Function

I tried using "w" instead of "d" in this module, but that did not change it for me.

So, I am looking for a way to calculate the difference between two dates, excluding weekends, where if the start and end date is the same, it returns zero, not a one.

Thanks!

 

[kmacd6951]

Re: DateDiff with "w" (weekday argument) - RESOLVE

Talking to myself a lot today!
I got this working now!