hello

[sms23]

Hello

have a table with the following: Destinations, sent date , return date

date in the table looks like this

London 11/12/04 11/14/04
London 15/12/04 18/12/04
Geneva 14/12/04 16/12/04


I need to calculate the average time it takes for destination to reach minus the weekends. Sat and Sun are not to be included in the caluculations.

Thanks in advance

Cheers

 

[krberube]

You can do a search for something like "datediffernce" and get quite a few posts. I found ( and use) the code below.
create a module called -- whatever you want. and paste in the below code, then call it using DateDiffW.

'Function to calculate business days, does NOT consider holidays!
Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If IsDate(BegDate) = False Then Exit Function
If IsDate(EndDate) = False Then Exit Function

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

 

[sms23]

thanks for the reply.
But not good in access. Do I need to create any tables etc

regards

 

[krberube]

no need to creat more tables.
goto modules, new, and paste in the code from above. when the you close the code window, you will be prompted to save the new module - yes, and call it something like -- DateDifference

then in your reports or wherever you can use this in your control source ---
=DateDiffW([sent date],[return date])

This will call the module you just created and give you the difference between the 2 dates.

Do a search for datedifference and you should see more posts on this also.