Date Diff excluding weekends and holidays 2

[rdefeo]

Happy Holidays to All!

I am an Intermediate user in MS-Access and I have a difficult challenge (for me, anyway!).

I have two fields I need to run functions on..."Create Date" and "ResolvedTIME."

I need the following results...
1) The number of weekdays (minus any Holidays) that the case was open.
2a) The total hours the case was open (minus weekends and holidays)
b)...based on a 7.5 hour work day.

I am not sure if I'm asking a lot, but any help would be greatly appreciated. Thank you!

Rich D

 

[Remou]

I think this should help:
Calculate working days between two dates
faq181-261

 

[r937]

instead of that faq's holidays table and calculating the weekends, why not extend the holidays table idea to a full-fledged calendar table

have one row per date, pre-load the calendar table a year or so in advance (i do mine from an excel sheet), and flag not only the holidays but also the weekend days

then it's a simple join, with a WHERE clause to eliminate the holidays and weekends, and GROUP BY and COUNT(*) to get the number of working days

r937.com | rudy.ca

 

[Melagan]

I use the DeltaDays function in my database and it works perfectly. You do not have to create a lookup table for every single weekend in time! Why would you want to do that when you can analyse weekend dates programatically? With the DeltaDays function, all you have to do is keep the Holidays table updated with your company's Holidays. I'd be happy to help you implement that function if you'd like.

 

[PHV]

Melagan, the bottom line is NO function call at all ...

 

[Melagan]

I might be missing something, and please educate me if I am, but what are the advantages of having a lookup table vs. calling a function? The way I see it, calling a function saves space in the database and saves time from having to create a huge lookup table.

Will a query run faster with the joined calendar table than with the VB function? I'm very curious now =)

"It's never too late to become what you might have been.

 

[PHV]

You can't use UDF (User Defined Function) in a recordset (either DAO or ADODB) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Maybe my vocabulary isn't very good on this subject, and I maintain that I am still a beginner myself, but isn't a query a recordset? If not then I stand better educated =) If so, though, then why does the following example SQL work?:

'[Escrow Number] is primary key
'SQLStart

SELECT t.[Escrow Number], t.[Opening Date], DeltaDays(t.[Opening Date], Date()) as BusDays
FROM Escrows t

Results are:
Escrow Number | Opening Date | BusDays
123456 12/27/2005 1
123457 12/27/2005 1
123458 12/28/2005 0

As always, I appreciate feedback and apologise in advance for having somewhat hi-jacked your thread, rdefeo. Hopefully things are still, at least, somewhat relevant though.


~Melagan
______
"It's never too late to become what you might have been.

 

[rdefeo]

Hi Melagan,

No, you did not hi-jack the thread. Modules and VBA are so new to me that anything I can learn is appreciated.

Your resolution sounds interesting. I could certainly use a hand implementing this.

I tried copying and modifying the DeltaDays faq sent by Remou above (FAQ181-261), but got very lost.

Thank you in advance!

Rich

 

[Remou]

Ah, can I withdraw my suggestion having seen R937's post ? So easy to change if a day suddenly becomes a work day, no need to tamper with code, everything can be done by a user. However, if you still wish to go with the FAQ, please post the code as modified.

 

[r937]

thank you




it's a great technique, especially the "can be done by the user" part, eh

r937.com | rudy.ca

 

[Remou]

r937, in case you miss it, another satisfied customer for you: Check a database for information, thread705-1171107

 

[r937]

thanks remou

once you've worked with a calendar table, you'll wonder how you ever got by without it, eh...

r937.com | rudy.ca

 

[MichaelRed]

The addition (change or deletion) to the holidays calendar is easuil mdified bu hte user with the addition od a simple form bound to the table. User modification of a table should always done via a form, so I do not see any advantage in the larger version. Further dissuasion for the use of the larger table (for me), is that some cultures do not use the Sat./Sun. weekend of the christian word. With a table, these differences need to be implemented across many records, while the simple "Holidays" (only) table is not concerned with these, so the code may be modified once and these process is corrected for all occurances.



MichaelRed

 

[r937]

michael, are you by any chance a programmer? ;-)

good table design and simple queries with no extraneous code is a much easier -- and portable -- development strategy

happy new year to everybody

r937.com | rudy.ca

 

[MichaelRed]

r937, are you by any chance an sql(er)?

Good application design considers the overall application. even the 'best' realtional db includes the use of "UDF"s, storage considerations and other individual needs / preferences.

While this is specifically the Queries and JET SQL forum, I prefer to consider the larger context.

MichaelRed

 

[r937]

me too, the larger context

and just because UDFs are possible, doesn't mean you have to make up a use for them, eh

;-)

r937.com | rudy.ca

 

[MichaelRed]

but then neither is one required to avoid them, eh? Under the umbrella of design & implementation, one coder's fish is just anothers' fowl? personally I take the approach that (for Ms. A. in particualr), there are alternative approaches to most everthing -and this is not just an accident. LIke wise, UDF's are not aluxury to be used only in case of necessity, but a necessity in themselves. They represent the unknown and (perhaps) unknowable needs of the user (and by extension designer / implementer) community. To quibble over the choices without some explicit rationale (e.g. demonstratable advantage / disadvantage) does not enhance the community or communication. I did not and do not begrudge your soloution, but offered an alternative which - FROM MY PERSPECTIVE AND EXPERIENCE - is soomewhat easier to maintain and use.




MichaelRed