INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return dates excluding weekends and holidays

Return dates excluding weekends and holidays

(OP)
I am working on a teaching db for courses. Known information, week start day, start date of course, number of days in the course, and holidays.

Given the start date and number of days I would like the query to return all dates for the class excluding weekends and holidays.

The reason for knowing the week start date is so it can be used in any country.

Can anyone help. Also, is there a way to set the work week in MS Access 2010 or do I have to use the weekday function everywhere?

Thanks,

RE: Return dates excluding weekends and holidays

(OP)
Great...I found a little hint and code to calculate the end date and used the holiday table too. Now I have to calculate all the workdays, non-holidays, between the two dates...use a query right...but what function should go with that so I don't get any holidays or weekends?

RE: Return dates excluding weekends and holidays

Do you have a record for every day somewhere for each class? From what you describe I do not think you do. So you cannot do it in a query unless you make a table of dates. But if you are loading a big table of dates then just add the column for weekend, holiday. You can use VBA to load that table. The other alternative is to do it dynamically, but I would just make the big table of dates.

RE: Return dates excluding weekends and holidays

This is the code I use to create a table of dates. I also have a module to determine if that date is a holiday. Then you can edit the holidays to add your specific school/buisness holiday

CODE

Public Sub FillDates()
  'Edit this to fill table
  Const tableName = "tblDates"
  Const StartDate = #1/1/2015#
  Const EndDate = #12/31/2016#
  
  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DayOfWeek As String
  Dim blnHoliday As Boolean
  
  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DayOfWeek = WeekdayName(Weekday(IterativeDate))
    DayOfWeek = sqlTxt(DayOfWeek)
    blnHoliday = IsHoliday(IterativeDate)
    'field names need to match your table
    strSql = "Insert into " & tableName & " (dtmDate, DayOfWeek, IsHoliday) values (" & strDate & ", " & DayOfWeek & ", " & blnHoliday & ")"
    IterativeDate = IterativeDate + 1
    Debug.Print strSql
    CurrentDb.Execute strSql
  Loop
  For i = 0 To numberOfDays
  Next i
End Sub

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function 

module for holidays

CODE

Public Function IsHoliday(ByVal dtmDate As Date) As Boolean
   Dim intYear As Integer
   Dim intMonth As Integer
   Dim intDay As Integer
   Dim intWeekDay As Integer
   intYear = Year(dtmDate)
   intMonth = Month(dtmDate)
   intDay = Day(dtmDate)
   intWeekDay = Weekday(dtmDate)
   
   'If you do not care if a holiday falls on a weekend
   'If intWeekDay > 1 And intWeekDay < 7 Then exit Function
    'New Years Day
     If DatePart("y", dtmDate) = 1 Then
       IsHoliday = True
       Exit Function
     End If
    'ML King 3rd Monday of Jan
       If DayOfNthWeek(intYear, 1, 3, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Presidents Day  3rd Monday of Feb
    If DayOfNthWeek(intYear, 2, 3, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
    End If
    'Memorial Day    Last Monday of May
      If LastMondayInMonth(intYear, 5) = dtmDate Then
         IsHoliday = True
         Exit Function
      End If
    'Independance Day
       If intMonth = 7 And intDay = 4 Then
          IsHoliday = True
          Exit Function
       End If
    'Labor Day   1st Monday of Sep
        If DayOfNthWeek(intYear, 9, 1, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Columbus Day    2nd Monday of Oct
        If DayOfNthWeek(intYear, 10, 2, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Thanksgiving Day  4th Thursday of Nov
       If DayOfNthWeek(intYear, 11, 4, vbThursday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'CHRISTMAS
        If intMonth = 12 And intDay = 25 Then IsHoliday = True
End Function
Public Function DayOfNthWeek(intYear As Integer, intMonth As Integer, N As Integer, vbDayOfWeek As Integer) As Date
  'Thanksgiving is the 4th thursday in November(11)
  'dayOfNthWeek(theYear,11,4,vbThursday)
   DayOfNthWeek = DateSerial(intYear, intMonth, (8 - Weekday(DateSerial(intYear, intMonth, 1), _
 (vbDayOfWeek + 1) Mod 8)) + ((N - 1) * 7))
End Function
Function LastMondayInMonth(intYear As Integer, intMonth As Long) As Date
    'Used for memorial day
    Dim LastDay As Date
    'define last day of the month of interest:
    LastDay = DateSerial(intYear, intMonth + 1, 0)
    'use to get last monday:
    LastMondayInMonth = LastDay - Weekday(LastDay, vbMonday) + 1
End Function 

So you query would be between start date and end date and day of week not Sat and Not Sun and not holiday.

My table of dates would look like

CODE


ID	dtmDate	dayOfWeek     isHoliday
1	1/1/2015	Thursday	     True
2	1/2/2015	Friday	     False
3	1/3/2015	Saturday	     False
4	1/4/2015	Sunday        False
5	1/5/2015	Monday        False
6	1/6/2015	Tuesday       False
7	1/7/2015	Wednesday     False
8	1/8/2015	Thursday	     False
9	1/9/2015	Friday        False
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close