Remove weekends from a date calculation

[Paul Dennis]

I have 2 dates/times which I need to take one from the other, however if
between these 2 dates there is a weekend I need to remove 48 hrs.

How can I check if a weekend falls between 2 dates? or for that matter a
number of weekends falls between 2 dates.

Any ideas - please?

 

[Sunil]

hi,

use the below function ..paste it into ur module

Function find_weekdays(BegDate As Variant, EndDate As Variant) As Integer

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
find_weekdays = Work_Days

End Function

and call this function from ur query with 2 parameters...

for eg:

select find_weekdays(#12/1/2005#,#12/12/2005#)-1 will return 7...this the
number of working days..i excluded saturday and sunday....

psl let me know if this helps u

thanks

with regds

Sunil.T

 

[Paul Dennis]

Created the module, but having problems with the select. Trying to put it in
using the Expression Builder but getting syntax errors. Tried in a seperate
query and used the SQL and managed to save the query but now get error
'Undefined function'.

Hence I have 2 problems - why is it an undefined function and then how to
have it as another field in a larger query?

sorry to be a pain.

 

[Chris2]

I have 2 dates/times which I need to take one from the other, however if
between these 2 dates there is a weekend I need to remove 48 hrs.

How can I check if a weekend falls between 2 dates? or for that matter a
number of weekends falls between 2 dates.

Any ideas - please?

Paul Dennis,

Calculate Number of Working Days:

http://www.mvps.org/access/datetime/date0006.htm


Sincerely,

Chris O.