Number of Days

[pbrown77]

Is there away, in a query, to count the number of week days between two dates?
Field 1 = startdate
field 2 = enddate
I know problems will happen when there is a holiday, but it would be nice to have a helping hand 99% of the time.

 

[Golom]

Have a look at faq181-261

 

[jgroh9]

pbrown77,

Here is a function you can use to pass the startdate and enddates to and it will return the number of weekdays between the 2 dates.

Public Function WeekDays(StartDate As Date, EndDate As Date) As Integer
Dim DaysDiff As Integer

'Get the total number of days between the 2 dates the user wants to check
DaysDiff = DateDiff("d", StartDate, EndDate)

'Initially set Weekdays to the number of days between the 2 dates being checked
WeekDays = DaysDiff

Do Until DaysDiff = 0
'Check to see if the value in StartDate is a weekend
If Weekday(StartDate) = vbSaturday Or Weekday(StartDate) = vbSunday Then
'Since the date being checked is a weekend decrease WeekDays by 1
WeekDays = WeekDays - 1
End If
'Decrease the DaysDiff so the loop is not an infinite loop
DaysDiff = DaysDiff - 1
'Increment the startdate so we have a new date to check
StartDate = StartDate + 1
Loop

'We need to add 1 day to include todays date so we get an accurate count
WeekDays = WeekDays + 1

End Function

You can pass the values into the function from the Query. Use the query builder tool to build the expression. Let me know if you need help on how to do this.