workingdays in Acess

[Johannes]

Dear Forum,

I hope you could help me with my new MS access 2000 challange.
I would like to calaculate in a query the different between 2 dates.
Output should be cleaned by Saturday and Sunday.

Example:
Date a: 5/12/2008
Date b: 5/9/2008

If I now calculate date a - date b result is 3 days.
I need to get the saturday and sunday out.
Is there any function available in access?

Many thanks for your support!
Johannnes

 

[Ron2006]

Here is a simple solution:

1) Compute the number of working days between two dates (watch
wordwrap):

workdays =DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1)) + 1

You now have the total number of weekdays between two dates.

The first datediff gives you all days, the second subtracts the number
of Saturdays and the third subtracts the number of Sundays.


2) Create a table of holidays.

write a query that selects all records in that table between the
specified dates and use

holidaycnt= dCount ("[fieldnameinholidaytable]",
"theabovecreatedquery")

Subtract result 2 from result 1 and you have the actual workdays. (Add
1 to result if first and last day are to be counted)

Ron

 

[Ron2006]

Here is a simple solution (watch word wrap):

1) Compute the number of working days between two dates:

workdays =DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1)) + 1

You now have the total number of weekdays between two dates.

2) Create a table of holidays.

write a query that selects all records in that table between the
specified dates and use

holidaycnt= dCount ("[fieldnameinholidaytable]",
"theabovecreatedquery")

Subtract 2 from1 and you have the actual workdays. (Add 1 to result if
first and last day are to be counted)1) Compute the number of working
days between two dates:

workdays =DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1)) + 1

You now have the total number of weekdays between two dates.

The first datediff gives you all days, the second subtracts the
Saturdays and the third subtracts the sundays.


2) Create a table of holidays.

write a query that selects all records in that table between the
specified dates and use

holidaycnt= dCount ("[fieldnameinholidaytable]",
"theabovecreatedquery")

Subtract 2 from1 and you have the actual workdays. (Add 1 to result if
first and last day are to be counted)

Ron