5 replies
by peyison
Here are two options:
There is a function called NetWorkdays that is part of the library msowcf.dll. This library ships with Office 2000. I don't know if it's available anywhere else, or not. If you can get it, here's how to use it:
Add a reference to the dll:
With a VBA window open, select Tools -> References... and check "Microsoft Office Web Components Function Library" and press OK.
The following code shows how to use it:
Sub test()
Dim tDate1 As Date
Dim tDate2 As Date
Dim oWebComp As MSOWCFLib.OCATP
Set oWebComp = New MSOWCFLib.OCATP
tDate1 = "1/1/01"
tDate2 = "1/31/01"
MsgBox oWebComp.NETWORKDAYS(tDate1, tDate2)
Set oWebComp = Nothing
End Sub
If you don't have access to this library, you can create a function to calculate workdays:
Public Function CalcWorkDays(atDateFrom As Date, atDateTo As Date) As Long
Dim dCounter As Double
Dim lWorkDays As Long
For dCounter = atDateFrom To atDateTo
Select Case WeekDay(dCounter)
Case vbSaturday, vbSunday
'skip these days
Case Else
lWorkDays = lWorkDays + 1
End Select
Next
CalcWorkDays = lWorkDays
End Function
Put this function in a code module.
You can then call this function from VBA:
Sub Test1()
MsgBox CalcWorkDays("1/1/01", "1/31/01")
End Sub
Or from a Query:
SELECT CalcWorkDays("1/1/01",[InvoiceDate]) AS Workdays
FROM tblInvoices;
Hope this helps.
by ert1962
by lo
Hi - I don't have an actual solution but look in Access Help for DateDiff (from that help: You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate thenumber of days between two dates, or the number of weeks between today and the end of the year.) and Weekday(date, [firstdayofweek]).
The first will give you Year, Quarter, Month, ... to Second between 2 dates. You can play with partial weeks using WeekDay to see where you are in the week.
May not be the 'real' ones you need. Look in Help for DATE... lo
by ert1962
by ert1962
This discussion is closed to new replies.
Trending Software posts
Sort by Trending