Convert Date Range to Work Week Hours

[dwaynesworld]

I have a report which generates values based on a date range [StartDate] &
[EndDate]. I am not sure that access can do this but what I need to be able
to do is have Access generate the number of days or hours are available
excluding weekends based on the date range. For example:

StartDate = 1/1/2008
EndDate = 1/17/2008
Total Days = 17
Business Days = 13 (4 weekend days during this range)

Any help on calculating this is greatly appreciated. Thanks

 

[Al Campagna]

Dwayne,
Create a loop, starting with your StartDate, and adding 1 day on each
iteration. (StartDate +1, +2, etc..)
During each iteration, determine if the Weekday(TempDate) = 7 (Sunday)
or 1 (Monday).
If it is, DON'T increment a counter variable... if it isn't, DO
increment a counter variable, and loop again until EndDate is reached.

Note: You'll probably want to exclude holidays from the count also.
Create a Holiday table with every holiday your company takes.
ex. HDay Holiday
7/4 4th of July
12/25 Christmas
Include a DLookup of the TempDate in the loop above, that checks to see
if that day is in the Holiday table. If exist = no count, if not = count
that day.

Check out Google Access Groups. There should be many "calculate
workdays" realated posts in there.
Can't seem to find my code I wrote to do that.... I'll post it on this
thread when/if I locate it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

..