Counting Working Days

[John Taylor]

Basically I have created a combo box called "Priority" with two fields - one
is text, the other is a number.

The data goes like this

Text field "High - within 14 working Days" the number field is (obviously)
"14"
Text field "Normal - within 28 working Days" the number field is (obviously)
"28"

I also have another field "Days" to add the number to a date - but I only
want to count working days - can this be done. For example if I open a
record on 5th January, then 14 working days is actually 18 days. However if
I open a record on say 9th January the actual days are 20.

The date fields I use are simply a "OpenedDate" and a "DueDate" - the number
from the combo box goes to a field called "Days"

I have an "OnChage" event in the combo box like this: -

Private Sub Priority_Change()

Me!days = Me!Priority.Column(1)

If Me!Priority = "Normal - within 28 working Days" Then

Me!DueDate = Me!OpenedDate + Me!Days

ElseIf Me!Priority = "High - within 14 working Days" Then
Me!DueDate = Me!OpenedDate + Me!Days


End If


End Sub

All my code works as it should - is there some way of "coding" working days
(or non working days".

All help will be greatly appreciated.

Using A2003

John Taylor

 

[Tom Wickerath]

Hi John,

Try one of the functions found here. The second one includes use of a table
to store company specific holidays, so that these days are not counted as
working days:

http://www.mvps.org/access/datetime/date0006.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Arvin Meyer [MVP]]

And in addition to the link that Tom points out, you can add or subtract
working days with:

http://www.datastrat.com/Code/GetBusinessDay.txt

 

[Tom Wickerath]

PS. Here is another set of functions:

http://www.mvps.org/access/datetime/date0012.htm

For example, given a table of holidays and today's date, you can calculate
the date that is X number of working days later (dhAddWorkDaysA function).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Taylor]

Thanks All - still struggling a bit but I can see the logic

John