General discussion

Locked

How to calculate weekdays in Access '97

By ert1962 ·
How do I calculate the difference between two dates in week/workdays (exclude saturdays & sundays)

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How to calculate weekdays in Access '97

by Peyison In reply to How to calculate weekdays ...

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.

Collapse -

How to calculate weekdays in Access '97

by ert1962 In reply to How to calculate weekdays ...

Poster rated this answer

Collapse -

How to calculate weekdays in Access '97

by Lo In reply to How to calculate weekdays ...

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

Collapse -

How to calculate weekdays in Access '97

by ert1962 In reply to How to calculate weekdays ...

Poster rated this answer

Collapse -

How to calculate weekdays in Access '97

by ert1962 In reply to How to calculate weekdays ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums