net working days in access

  • Thread starter Thread starter Christina
  • Start date Start date
C

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
 
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
 
Back
Top