can you limit date calculations to exclude weekend dates?

  • Thread starter Thread starter ART
  • Start date Start date
A

ART

i simply want to know if an access query can be designed to calculate a date
during a typical US workweek (Mon-Friday) and avoid weekend days.

Also, could there be a setting to avoid holidays as well. Possibly if I set
them in a table?
 
Is this helpful for weekdays?

SELECT IIf(Weekday([myDate])=1,Null,IIf(Weekday([myDate])=7,Null,[myDate]))
AS WD, Table2.myOtherData
FROM Table2;
 
This is what's needed to handle both Weekends AND Holidays (July 4th in this
case) of your choosing (the Query calls a Function inside a module:

SELECT Table2.myDate
FROM Table2
WHERE (((Table2.myDate)=GetWeekDays([myDate])));

Function GetWeekDays(myDate)

If Weekday(myDate) = 1 Or Weekday(myDate) = 7 Then
GetWeekDays = Null
ElseIf myDate = #7/4/2009# Then
GetWeekDays = Null
Else
GetWeekDays = myDate
End If

End Function
 
This is what's needed to handle both Weekends AND Holidays (July 4th in this
case) of your choosing (the Query calls a Function inside a module:

SELECT Table2.myDate
FROM Table2
WHERE (((Table2.myDate)=GetWeekDays([myDate])));

Function GetWeekDays(myDate)

If Weekday(myDate) = 1 Or Weekday(myDate) = 7 Then
GetWeekDays = Null
ElseIf myDate = #7/4/2009# Then
GetWeekDays = Null
Else
GetWeekDays = myDate
End If

End Function

ART said:
i simply want to know if an access query can be designed to calculate a date
during a typical US workweek (Mon-Friday) and avoid weekend days.

Also, could there be a setting to avoid holidays as well. Possibly if I set
them in a table?
Click to expand...
Click to expand...

The problem with this method to exclude holiday dates is that the
actual date of some holidays changes each year, i.e. while in the U.S.
New Years day is always Jan. 1st, others like Memorial Day, Labor
Day, Veterans day, etc. all have dates that vary by the year, and some
holidays in one state may not be a holiday at all in other states, and
certainly not in other areas of the world.

Here is a web site you can visit for more information:
http://www.infoplease.com/ipa/A0002069.html

Just search for 'State Holidays' for additional sites.
 
Your assistance is highly appreciate for the following:

In MS Office 2007. I have two the following fields:

Start Date, Stop Date, Duration.

Duration = is the different between Stop Date and Start Date (excluding weekend and holilday)

Holidays are from another table called "HOLIDAY"

With many thanks, Navy
 
Back
Top