weekday calculation problem?!?

[ros794]

Hi,

I have been trying to calculate date difference not including weekends. I found this code to use in another thread, but am unsure of where to put it.
I am quite new to using access, so a detailed description would be very useful!!!

Here's the code posted:

intDaysInPeriod = DateDiff("d", StartDate, EndDate)
intSaturdays = DateDiff("ww", StartDate, EndDate, vbSaturday)
intSundays = DateDiff("ww", StartDate, EndDate, vbSunday)

intWeekDays=intDaysInPeriod - intSaturdays - intSundays


Thanks in advance!!

Rosx

 

[snoko]

Rosx,

Try this, insert this into a module and then call this from your form where you require the calculation

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
Rem Extracted from TechNet ref.Q210562
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

'If Null then insert current date
If IsNull(BegDate) Then
BegDate = Date
Else
BegDate = DateValue(BegDate)
End If

If IsNull(EndDate) Then
EndDate = Date
Else
EndDate = DateValue(EndDate)
End If

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

 

[ros794]

Hi,

I've tried your formula, and the only fields I am using are LOADDATE(startdate) and DATUM (enddate)
I have replaced these into the start and end date names in your formula, is this all I woyld need to do, or do I need to make other fields etc.

Also I am having trouble running this in my form, all I want to insert in the form is the name of the customer and the number of days between the start and end in question.

Sorry to be such a pain, but this is my first venture into the land of 'modules'!!

Thanks!!!!!

Ros