Weekday/Workday Time Calculations 1

[jabrony76]

Hi all,

I work in the QI Department of an Insurance Company and have a database set up to calculate the time difference between when an appeal was received and when the final determination occurred. Here in lies the question... Weekends and Holidays do not count as accountable time for us so is there a way to set the fields to workdays only. I have been running scrub reports of the out of complaince records for manual verification of weekends/holidays.
I consulted the assistant and I spoke of a weekday function but when I tried the expression it gave me it didn't work. Any ideas?
Thanks,
Andy M.
Jabrony76@aol.com

 

[GingerR]

Hi there--
This is a common question in my company's tech forum too. You have to set up what your company's holidays are. The way i do it which might not be the slickest code-wise but it's easy after you get it set up and it's what everyone i know uses at work:

The easiest way to create this table is in Excel (because you can do the "=B2+1" formula to create a list of dates easily), then import or copy the data into Access.

Create a table with fields WorkDay and Date
In Date, put in all the consecutive dates in the universe, or whatever you think you'll be using, for example 1/1/01 thru 12/31/01. You can always add to it later.

In the WorkDay field, just start numbering with say 1000 (just in case you have to put in some previous dates some day). Each DATE that is a valid workday gets the next highest WORKDAY value. But weekends and holidays dont get incremented. like this for thanksgiving 2000 for example:

1233 11/22/00 (M)
1234 11/21/00 (Tu)
1235 11/22/00 (Wed-Work)
1235 11/23/00 (Th-Holiday)
1235 11/24/00 (Fr-Holiday)
1235 11/25/00 (SA-Weekend)
1235 11/26/00 (Su-Weekend)
1236 11/27/00 (M)
1237 11/28/00 (Tu)

once you have this in Access, then just use it in your queries by linking to it to find the WORKDAY of the appeal submittal and subtract that WORKDAY from the WORKDAY of the final determination. If you have to get it down to the minute, you can do some manipulation with the times of the submittal and determination and subtract those .

Good Luck.

g

 

[MichaelRed]

see faq181-184


MichaelRed
redmsp@erols.com

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

 

[skenglish]

Hi Ginger,

I have read all the solutions to counting the number of working days between two dates. It seems that your solution is the easiest to implement (read "I don't know anything about Visual Basic--yet.&quot

However, I'm brain-dead today. Can you tell me how, in a query, to take the dates from my data entry table (populated by users) and get the corresponding WorkDay value from my working days table in order to do the subtraction between the starting and ending dates?

I hope Microsoft creates this function soon. It's evident that it's a common need.

Thanks for any help and for bearing with me.

Sandy

 

[skenglish]

Never mind. It's a new day, my brain is clearer, I figured it out!

Sandy