Calculating on 5 day work week

[gslords]

I have a client who needs a number of days calculated between to dates, but only wants to see workdays (M-F). I have been able to do this by summing all the individual weekdays with the (ww function) and then summing those totals, but there must be an easier way.

Any suggestions?

Tim Wright

 

[ColinEssex]

I've got some code that'll do it. Email me if you need it and I'll rake it out.

Col

 

[raskew]

Tim-

Here are a couple of methods you can play with...

Function CalcWkDays(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays(#06/28/02#, #07/01/01#)
'output: 1

Dim x As Integer

x = DateDiff("d", dteStartDate, dteEndDate) _
    - 2 * DateDiff("ww", dteStartDate, dteEndDate) _
    + IIf(WeekDay([dteStartDate]) = 7, 1, 0)
CalcWkDays = x
End Function

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays2(#01/01/01#, #07/01/01#)
'output: 129
'NOTE:As written,this counts both start and end dates.

Dim n As Integer

n = 0

Do While dteStartDate <= dteEndDate

'17 in the following expression represents the numeric
'days of week for Sunday(1) and Saturday (7)

n = n + IIf(InStr("17", WeekDay(dteStartDate)) = 0, 1, 0)
dteStartDate = dteStartDate + 1

Loop

CalcWkDays2 = n
End Function

Bob