net working days in access

[Christina]

I have a table with dates ...beginning date and ending date. and would like
to run a query to determine the net working days.

Thanks

 

[Michel Walsh]

DateDiff("d", d1, d2) returns the 'gross' number of days between the two
dates (excluding one of the two dates, including the other)

DateDiff("ww", d1, d2, 1) returns the number of Sunday between the two
dates (excluding the first one, including the last one)

DateDiff("ww", d1, d2, 7) returns the number of Saturday between the two
dates (excluding the first one, including the last one)

So, ***IF*** the two dates are neither a Sunday, neither a Saturday, and if
the last day has to be counted too:


1+ DateDiff("d", d1, d2) - DateDiff("ww", d1, d2, 1) - DateDiff("ww",
d1, d2, 7)


returns the number of weekdays. Sure, you also have to subtract the Holiday
(probably held in a table), maybe with a

DCount("*", "Holidays", "holiday BETWEEN " & Format(d1,
"\#mm/dd/yyyy\#") & " AND " & Format(d2, "\#mm/dd/yyyy\#") )


and that assumes your table of holiday do NOT list holiday falling on a
Sunday or on a Saturday (else, we would be removing the holiday twice).


To be sure d1 is neither a Saturday, neither a Sunday, you can replace it,
in the previous equations, with:

d1 + Choose( DatePart("w", d1), 1, 0, 0, 0, 0, 0, 2)

and d2 with:

d2 - Choose( DatePart("w", d2), -2, 0, 0, 0, 0, 0, -1)



Sure, that becomes quite complex "in a one line", so you probably want to
define the whole expression inside a user defined VBA function, in a
standard module.





Vanderghast, Access MVP

 

[MGFoster]

I have a table with dates ...beginning date and ending date. and would like
to run a query to determine the net working days.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This subject has been discussed many times on this and other Access news
groups (like comp.databases.ms-access). Included is a function I picked
up from one of those discussions. You can call it from a query like
this:

SELECT Work_Days(begin_date, end_date) As workDays
FROM table_name
.... etc. ...

Where begin_date and end_date are columns in the table.

It does not exclude holidays since that is a variable best handled by a
calendar table of holidays.

Use Google groups advanced search to find more discussions of work day
queries.


Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Day(DateCnt) Mod 6 <> 1 Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSa2n9oechKqOuFEgEQInzQCfZEm+PNO+zDEv0dHVcHBFM1ENn4kAoJVD
uqxLrdimuLdcsAX+SiAZQ+4t
=IXvU
-----END PGP SIGNATURE-----