calculating elapsed days while excluding work and holidays

[leeroi]

I want to track the number of work days that an employee takes to complete a report. For each report, there are at least 3 dates to track. First, the clincian's date of dictation, second the date the secretary notifies the clinician the report is typed, and third, the date that the clinician proofs the report.

I know I need some kind of function, and a table which lists the dates of specific holidays to exclude. I know that I somehow need to make these work with a query so that when I compute date differences in days, weekends and holidays are excluded.

Unfortunately, that's about the limit of my knowledge. Could anyone give me a hand with this? A sample function and how to apply it to the query would be very helpful.

Lee

 

[JaneInMA]

This explained in the Faq for this Module with sample code
Section 10 a
I havent tried this faq but all the ones I have have worked excellently

 

[rafe]

Found this function while looking thru A2K help... Rather than reinvent things I thought I might give it a try...

however, altho it works great in Excel I haven't gotten it to work in Access yet, referencing msowcf.dll isn't helping ... i'll keep you posted
============================================================
Cut & past from help
============================================================
NETWORKDAYS
Returns the number of whole working days between a start and end date, excluding weekends and any identified holidays.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

 

[MichaelRed]

See faq181-261 it was written for Ms. Access '97.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over