I would like to calculate the number of "work days" between two dates;
however, the "regular days off" are may not be Sat and Sun and they
may not be consecutive. Is there a way to calculate the number of
days between two dates that deducts certain "days off"? Thus, it
could calculate the number of work days for a person with Mondays and
Thursdays off and then calculate the number of work days for a person
with Saturdaya and Mondays off. (I would ove to be able to enter the
two days off and then get the number of work days between two dates
which keeps in mind the days off entered. Any/all ideas are greatly
appreciated!!

Thank you in advance!!

blah (AT) blah (DOT) com wrote:

I got the following functions at
http://www.mvps.org/access/datetime/date0011.htm

I modified it slightly. I added intDayOff1/2 to the code. If you look
at the Weekday function Sunday is 1...Saturday 7. So if the days off
are sat/sun, you also pass 6/7, if mon, fri you pass 2/6. If there are
no days off, 0.

I want the workdays between 4/6 and 4/16/2010 with Mon and Sat off
Ex: ? HowManyWeekDay(#4/6/2010#, Date(),2,7)
9
Only Sunday off
? HowManyWeekDay(#4/6/2010#, Date(),1,0)
11

Public Function HowManyWeekDay(FromDate As Date, _
ToDate As Date, _
intDateOff1 As Integer, _
intDateOff2 As Integer, _
Optional ToDateIsIncluded As Boolean = True)

HowManyWeekDay = DateDiff("d", FromDate, ToDate) - _
ToDateIsIncluded - _
HowManyWD(FromDate, ToDate, intDateOff1) - _
HowManyWD(FromDate, ToDate, intDateOff2)
End Function

Public Function HowManyWD(FromDate As Date, _
ToDate As Date, _
WD As Integer) As Integer
' No error handling actually supplied
If WD > 0 Then HowManyWD = _
DateDiff("ww", FromDate, ToDate, WD) _
- Int(WD = Weekday(FromDate))
End Function

On Fri, 16 Apr 2010 20:34:10 -0700, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:


Thank you very much for the code. I have to admit, I am not an
advanced Access user. Your knowledge has been awsome, but it is a bit
over my head. Here's my situation: I ahve an Access database that
contains a table which has a field for the date a person goes out, a
field for the date they return, a drop-down fields for the person's
two days off (linked to a table with the days of the week with
autonumber for 1 for Sunday through 7 for Saturday). I would like to
have a table field calculate the number of days (excluding the
particular regular days off) between the date the person goes out and
the return date and/or a report that can do the same thing.

I'm not sure how to impliment the above code (e.g., if I enter it as a
module, how do I use it?)

I appreciate your help and patience!! I have learned a decent about
Access; however, it is all self-taught. I may not be using the
correct terms.

Thanks!!

blah (AT) blah (DOT) com wrote:
Is this being done from a table? Or a form? I'll pretend a form.

First, go to Modules, select New, and copy paste the code from my
routine above into it. Save it. Call it Module1 if you like. If
doesn't matter. Close the module

Click on Forms. Design a new form and follow along.

Add two textboxes; FromDate and ToDate. Add two comboboxes
ComboOffDay1 and ComboOffDay2. In the property sheet, under Other, you
change the names to the above.

Both combo's have a default value of 0. They each display the same two
columns. The first column's values are 0..7. The second columns are
"No Day Off, Monday, Tuesday...Sunday". Hide the first column. You can
do the above by the wizard when you create the combos. You'd type the
values in if you don't use a table and column widths could be 0;1 in the
property sheet. With me so far?

Now add the last textbox. Call it DaysWorked. Default value is 0. In
the ControlSource field enter the following:
=CalcWorkDays().

Ok. Now open the code module for the form. Copy and paste the following.

Function CalcWorkDays() As Long
If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
CalcWorkDays = HowManyWeekDay(CDate(Me.FromDate),
CDate(Me.ToDate), Me.DayOff1, Me.DayOff2)
End If
End Function
Private Sub DayOff1_AfterUpdate()
Me.DaysWorked.Requery
End Sub
Private Sub DayOff2_AfterUpdate()
Me.DaysWorked.Requery
End Sub
Private Sub FromDate_AfterUpdate()
Me.DaysWorked.Requery
End Sub
Private Sub ToDate_AfterUpdate()
Me.DaysWorked.Requery
End Sub

If you open the property sheet, click on Events, and then click on each
field except DaysWorked you'll see [Event Procedure]. If you don't,
click the down arrow in the AfterUpdate event.

OK. Save the form and run/open it. Now enter some dates, change the
off day values, and the textbox will be updated with the correct nunmber
of workdays.

Does it work as you expect. BTW, simply reading this without doing it
will be fruitless for you.

Here's the deal. You need a starting day and an ending day. You need 2
values 0..7, that represent dayoff 1 & 2. If you have that information,
you can even create a query to calc it.

I'll pretend you have these fields in a table and that these are the
field names. In the query builder drop your four fields into the
columns; FromDate, ToDate, DayOff1 (DO1) , DayOff2 (DO1). I used DO1 so
the line below doesn't wrap. DO1/2 are 0..7 to represent days. Then
enter the following
DaysWorked : HowManyWeekDay(CDate(FromDate), CDate(ToDate), DO1, DO2)

Now if you save and run the query, the 5th column will show the days worked.

On Sun, 18 Apr 2010 19:20:11 -0700, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:


Thank you. I actually have the data saved in two tables (one has the
th date the person went "out" and the date the person "returned" and
the second has the days off (two fields with Sunday = 1 through
Saturday = 7). I would like to have a field in the first table (if
possible) that would calculate the number of work days between the the
date the person went "out" and the date the person "returned" minus th
two days off. I wouldlike to add to tis by saying that the date the
person returned may be added later....can this be ok with the return
date being blank until it is entered?

THANK YOU, Salad!!

blah (AT) blah (DOT) com wrote:
Well, if you open a new query, add the two tables, you should be able to
do it. I assume there's an EmpID that you can link between the two
tables. If not, your database isn't up to snuff. Once you have the two
tables, drag the out, in, and days off fields to columns in the query
builder. Then copy paste the following into the 5th column.
DaysWorked : HowManyWeekDay(CDate(FromDate), CDate(ToDate), DO1, DO2)
Change the names as required to reflect you field names in the table.
Of course, you need to copy/paste the 2 functions into a code module.
Then run the query.

Beyond that, I don't know what I can offer. Maybe go to Barnes and
Noble, grab an Access book, and read about functions and how they work.

Good luck.