Adding working days to a date (1 Viewer)

Minty

Minty

AWF VIP
Local time
Today, 07:39
Joined
Jul 26, 2013
Messages
10,366
You would need to create a loop and count very similar to the one I created earlier; Again air code of the top of my head

Code: 
Public Function CountHols(Startdate as date, enddate as Date) as Integer

Dim iDayLoop as Long
Dim dCheckdate as Date

CountHols = 0
iDayloop = DateDiff("d", Startdate, enddate) +1 ' Not sure if you need the +1 here you'll soon find out :)
iCount = 0
While iCount < iDayloop 
      dCheckdate = startdate + iCount
      CountHols = CountHols + DCount("DateFieldInYourHolidayTable", "YourHolidayTable", "[DateFieldInYourHolidayTable] = #" & dCheckDate & "#")
      iCount= iCount + 1
Wend

End Function
 
E

ECEK

Registered User.
Local time
Today, 07:39
Joined
Dec 19, 2012
Messages
717
Minty. You're Mint.

I'll look into the date formatting issue. Thanks for the direction. Your help has/is invaluable lets hope we can nail this for the common good.

I'll get back to you with my findings.
 
Minty

Minty

AWF VIP
Local time
Today, 07:39
Joined
Jul 26, 2013
Messages
10,366
After spending more than 2 minutes on this and seeing JHB's post you could just use a better DCount and avoid the loop.

CountHols = DCount("DateField","YourHolidayTable","[Datefield] Between #" & StartDate & "# AND #" EndDate &"#")
 
E

ECEK

Registered User.
Local time
Today, 07:39
Joined
Dec 19, 2012
Messages
717
Solved

I found this code that solves the problem.
The whole process runs like this.
I have a StartDate and a number of weekdays to add to it to get my EndDate.
By using this code I can calculate how many workingdays AND holidays (in my tblHoliday table) there are between the StartDate and the EndDate.

If My WorkingDays2 function gives me less than the number of days I've initially added then I need to add an extra day (or two).

Kudos to Arvin Meyer Esq. Many thanks and also to posters. Your patience is greatly appreciated.

Code: 
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
 
W

whatdafaq

New member
Local time
Today, 09:39
Joined
Apr 8, 2018
Messages
1
Here is my custom workday function as an alternative...

Code: 
Public Function WorkDay(ByVal start_date As Date, ByVal days As Long, Optional ByVal weekend As Integer = 0, Optional ByVal holidays As Boolean = False) As Date
'   Custom WorkDay calculation function like MS Excel's Workday.Intl()
'
'   Author        : Adil Kocaosmanlar // adilkocaosmanlar@gmail.com
'   Date          : 2019/09/19
'   Usage         : WorkDay(start_date, days, weekend, holidays)
'                   - start_date    : Required. The start date, truncated to integer.
'                   - days          : Required. The number of workdays before or after the start_date. A positive value yields a future date;
'                                     a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.
'                   - weekend       : Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is
'                                     a weekend number or string that specifies when weekends occur.Weekend number values indicate the following
'                                     weekend days:
'                                     1 - Saturday, Sunday
'                                     2 - Sunday, Monday
'                                     3 - Monday, Tuesday
'                                     4 - Tuesday, Wednesday
'                                     5 - Wednesday, Thursday
'                                     6 - Thusday, Friday
'                                     7 - Friday, Saturday
'                                     11  - Sunday only
'                                     12  - Monday only
'                                     13  - Tuesday only
'                                     14  - Wednesday only
'                                     15  - Thursday only
'                                     16  - Friday only
'                                     17  - Saturday only
'                   - holidays      : Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays
'                                     shall be defined in tbl_holidays table. The ordering of dates in holidays can be arbitrary.
'
'   Example       : WorkDay("2019/09/19", 3, 1, True)
On Error GoTo Err_Procedure
  Dim n As Long
  Dim NextDate As Date
  Dim Step As Long
  Dim HolidayMatchCount As Long
  If (weekend > 0 And weekend < 8) Or (weekend > 10 And weekend < 18) Then
    n = 0
    Step = Sgn(days)
    NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
    Do Until n >= Abs(days)
      NextDate = NextDate + Step
      If holidays Then ' Count all work days except holidays defined in tbl_holidays table.
        HolidayMatchCount = DCount("holiday_date", "tbl_holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        Select Case weekend
          Case 1 ' Saturday, Sunday and Holiday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 2 ' Sunday, Monday and Holiday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 3 ' Monday, Tuesday and Holiday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 4 ' Tuesday, Wednesday and Holiday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 5 ' Wednesday, Thursday and Holiday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 6 ' Thursday, Friday and Holiday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 7 ' Friday, Saturday and Holiday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
          Case 11  ' Sunday and Holiday
            If Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 12  ' Monday and Holiday
            If Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 13  ' Tuesday and Holiday
            If Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 14  ' Wednesday and Holiday
            If Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 15  ' Thursday and Holiday
            If Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 16  ' Friday and Holiday
            If Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 17  ' Saturday and Holiday
            If Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
        End Select
    Else ' Count all work days.
        Select Case weekend
          Case 1 ' Saturday, Sunday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 2 ' Sunday, Monday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 3 ' Monday, Tuesday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 4 ' Tuesday, Wednesday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 5 ' Wednesday, Thursday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 6 ' Thursday, Friday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 7 ' Friday, Saturday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday Then n = n + 1
          Case 11  ' Sunday only
            If Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 12  ' Monday only
            If Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 13  ' Tuesday only
            If Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 14  ' Wednesday only
            If Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 15  ' Thursday only
            If Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 16  ' Friday only
            If Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 17     ' Saturday only
            If Weekday(NextDate) <> vbSaturday Then n = n + 1
        End Select
      End If
    Loop
    WorkDay = NextDate
  Else
    If holidays Then ' Count all days except holidays defined in tbl_holidays.
      n = 0
      Step = Sgn(days)
      NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
      Do Until n >= Abs(days)
        NextDate = NextDate + Step
        HolidayMatchCount = DCount("holiday_date", "tbl_Holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        If HolidayMatchCount < 1 Then n = n + 1
      Loop
      WorkDay = NextDate
    Else ' Count all days.
      WorkDay = DateAdd("d", days, Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays))
    End If
  End If

Exit_Procedure:
  Exit Function

Err_Procedure:
  Dim strErrorMessage As String
  Select Case Err.Number
    Case 0  'Everything is fine :)
    Case Else
    strErrorMessage = "We got an unexpected error, please note these details:" & vbCrLf & _
                      "Error number: " & Err.Number & vbCrLf & _
                      "Error description: " & Err.Description & vbCrLf & _
                      "Error source: " & Err.Source & vbCrLf & _
                      "Session: " & Environ("computername") & "\" & Environ("username")
  End Select
  Debug.Print strErrorMessage
  Resume Exit_Procedure
End Function
 
Last edited:
N

Number11

Member
Local time
Today, 07:39
Joined
Jan 29, 2020
Messages
607
@
whatdafaq said:
Here is my custom workday function as an alternative...

Code: 
Public Function WorkDay(ByVal start_date As Date, ByVal days As Long, Optional ByVal weekend As Integer = 0, Optional ByVal holidays As Boolean = False) As Date
'   Custom WorkDay calculation function like MS Excel's Workday.Intl()
'
'   Author        : Adil Kocaosmanlar // adilkocaosmanlar@gmail.com
'   Date          : 2019/09/19
'   Usage         : WorkDay(start_date, days, weekend, holidays)
'                   - start_date    : Required. The start date, truncated to integer.
'                   - days          : Required. The number of workdays before or after the start_date. A positive value yields a future date;
'                                     a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.
'                   - weekend       : Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is
'                                     a weekend number or string that specifies when weekends occur.Weekend number values indicate the following
'                                     weekend days:
'                                     1 - Saturday, Sunday
'                                     2 - Sunday, Monday
'                                     3 - Monday, Tuesday
'                                     4 - Tuesday, Wednesday
'                                     5 - Wednesday, Thursday
'                                     6 - Thusday, Friday
'                                     7 - Friday, Saturday
'                                     11  - Sunday only
'                                     12  - Monday only
'                                     13  - Tuesday only
'                                     14  - Wednesday only
'                                     15  - Thursday only
'                                     16  - Friday only
'                                     17  - Saturday only
'                   - holidays      : Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays
'                                     shall be defined in tbl_holidays table. The ordering of dates in holidays can be arbitrary.
'
'   Example       : WorkDay("2019/09/19", 3, 1, True)
On Error GoTo Err_Procedure
  Dim n As Long
  Dim NextDate As Date
  Dim Step As Long
  Dim HolidayMatchCount As Long
  If (weekend > 0 And weekend < 8) Or (weekend > 10 And weekend < 18) Then
    n = 0
    Step = Sgn(days)
    NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
    Do Until n >= Abs(days)
      NextDate = NextDate + Step
      If holidays Then ' Count all work days except holidays defined in tbl_holidays table.
        HolidayMatchCount = DCount("holiday_date", "tbl_holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        Select Case weekend
          Case 1 ' Saturday, Sunday and Holiday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 2 ' Sunday, Monday and Holiday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 3 ' Monday, Tuesday and Holiday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 4 ' Tuesday, Wednesday and Holiday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 5 ' Wednesday, Thursday and Holiday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 6 ' Thursday, Friday and Holiday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 7 ' Friday, Saturday and Holiday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
          Case 11  ' Sunday and Holiday
            If Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 12  ' Monday and Holiday
            If Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 13  ' Tuesday and Holiday
            If Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 14  ' Wednesday and Holiday
            If Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 15  ' Thursday and Holiday
            If Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 16  ' Friday and Holiday
            If Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 17  ' Saturday and Holiday
            If Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
        End Select
    Else ' Count all work days.
        Select Case weekend
          Case 1 ' Saturday, Sunday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 2 ' Sunday, Monday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 3 ' Monday, Tuesday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 4 ' Tuesday, Wednesday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 5 ' Wednesday, Thursday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 6 ' Thursday, Friday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 7 ' Friday, Saturday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday Then n = n + 1
          Case 11  ' Sunday only
            If Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 12  ' Monday only
            If Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 13  ' Tuesday only
            If Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 14  ' Wednesday only
            If Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 15  ' Thursday only
            If Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 16  ' Friday only
            If Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 17     ' Saturday only
            If Weekday(NextDate) <> vbSaturday Then n = n + 1
        End Select
      End If
    Loop
    WorkDay = NextDate
  Else
    If holidays Then ' Count all days except holidays defined in tbl_holidays.
      n = 0
      Step = Sgn(days)
      NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
      Do Until n >= Abs(days)
        NextDate = NextDate + Step
        HolidayMatchCount = DCount("holiday_date", "tbl_Holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        If HolidayMatchCount < 1 Then n = n + 1
      Loop
      WorkDay = NextDate
    Else ' Count all days.
      WorkDay = DateAdd("d", days, Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays))
    End If
  End If

Exit_Procedure:
  Exit Function

Err_Procedure:
  Dim strErrorMessage As String
  Select Case Err.Number
    Case 0  'Everything is fine :)
    Case Else
    strErrorMessage = "We got an unexpected error, please note these details:" & vbCrLf & _
                      "Error number: " & Err.Number & vbCrLf & _
                      "Error description: " & Err.Description & vbCrLf & _
                      "Error source: " & Err.Source & vbCrLf & _
                      "Session: " & Environ("computername") & "\" & Environ("username")
  End Select
  Debug.Print strErrorMessage
  Resume Exit_Procedure
End Function
Click to expand...
How do i call this to update a text box?
 
N

Number11

Member
Local time
Today, 07:39
Joined
Jan 29, 2020
Messages
607
whatdafaq said:
Here is my custom workday function as an alternative...

Code: 
Public Function WorkDay(ByVal start_date As Date, ByVal days As Long, Optional ByVal weekend As Integer = 0, Optional ByVal holidays As Boolean = False) As Date
'   Custom WorkDay calculation function like MS Excel's Workday.Intl()
'
'   Author        : Adil Kocaosmanlar // adilkocaosmanlar@gmail.com
'   Date          : 2019/09/19
'   Usage         : WorkDay(start_date, days, weekend, holidays)
'                   - start_date    : Required. The start date, truncated to integer.
'                   - days          : Required. The number of workdays before or after the start_date. A positive value yields a future date;
'                                     a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.
'                   - weekend       : Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is
'                                     a weekend number or string that specifies when weekends occur.Weekend number values indicate the following
'                                     weekend days:
'                                     1 - Saturday, Sunday
'                                     2 - Sunday, Monday
'                                     3 - Monday, Tuesday
'                                     4 - Tuesday, Wednesday
'                                     5 - Wednesday, Thursday
'                                     6 - Thusday, Friday
'                                     7 - Friday, Saturday
'                                     11  - Sunday only
'                                     12  - Monday only
'                                     13  - Tuesday only
'                                     14  - Wednesday only
'                                     15  - Thursday only
'                                     16  - Friday only
'                                     17  - Saturday only
'                   - holidays      : Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays
'                                     shall be defined in tbl_holidays table. The ordering of dates in holidays can be arbitrary.
'
'   Example       : WorkDay("2019/09/19", 3, 1, True)
On Error GoTo Err_Procedure
  Dim n As Long
  Dim NextDate As Date
  Dim Step As Long
  Dim HolidayMatchCount As Long
  If (weekend > 0 And weekend < 8) Or (weekend > 10 And weekend < 18) Then
    n = 0
    Step = Sgn(days)
    NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
    Do Until n >= Abs(days)
      NextDate = NextDate + Step
      If holidays Then ' Count all work days except holidays defined in tbl_holidays table.
        HolidayMatchCount = DCount("holiday_date", "tbl_holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        Select Case weekend
          Case 1 ' Saturday, Sunday and Holiday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 2 ' Sunday, Monday and Holiday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 3 ' Monday, Tuesday and Holiday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 4 ' Tuesday, Wednesday and Holiday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 5 ' Wednesday, Thursday and Holiday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 6 ' Thursday, Friday and Holiday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 7 ' Friday, Saturday and Holiday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
          Case 11  ' Sunday and Holiday
            If Weekday(NextDate) <> vbSunday And HolidayMatchCount < 1 Then n = n + 1
          Case 12  ' Monday and Holiday
            If Weekday(NextDate) <> vbMonday And HolidayMatchCount < 1 Then n = n + 1
          Case 13  ' Tuesday and Holiday
            If Weekday(NextDate) <> vbTuesday And HolidayMatchCount < 1 Then n = n + 1
          Case 14  ' Wednesday and Holiday
            If Weekday(NextDate) <> vbWednesday And HolidayMatchCount < 1 Then n = n + 1
          Case 15  ' Thursday and Holiday
            If Weekday(NextDate) <> vbThursday And HolidayMatchCount < 1 Then n = n + 1
          Case 16  ' Friday and Holiday
            If Weekday(NextDate) <> vbFriday And HolidayMatchCount < 1 Then n = n + 1
          Case 17  ' Saturday and Holiday
            If Weekday(NextDate) <> vbSaturday And HolidayMatchCount < 1 Then n = n + 1
        End Select
    Else ' Count all work days.
        Select Case weekend
          Case 1 ' Saturday, Sunday
            If Weekday(NextDate) <> vbSaturday And Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 2 ' Sunday, Monday
            If Weekday(NextDate) <> vbSunday And Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 3 ' Monday, Tuesday
            If Weekday(NextDate) <> vbMonday And Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 4 ' Tuesday, Wednesday
            If Weekday(NextDate) <> vbTuesday And Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 5 ' Wednesday, Thursday
            If Weekday(NextDate) <> vbWednesday And Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 6 ' Thursday, Friday
            If Weekday(NextDate) <> vbThursday And Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 7 ' Friday, Saturday
            If Weekday(NextDate) <> vbFriday And Weekday(NextDate) <> vbSaturday Then n = n + 1
          Case 11  ' Sunday only
            If Weekday(NextDate) <> vbSunday Then n = n + 1
          Case 12  ' Monday only
            If Weekday(NextDate) <> vbMonday Then n = n + 1
          Case 13  ' Tuesday only
            If Weekday(NextDate) <> vbTuesday Then n = n + 1
          Case 14  ' Wednesday only
            If Weekday(NextDate) <> vbWednesday Then n = n + 1
          Case 15  ' Thursday only
            If Weekday(NextDate) <> vbThursday Then n = n + 1
          Case 16  ' Friday only
            If Weekday(NextDate) <> vbFriday Then n = n + 1
          Case 17     ' Saturday only
            If Weekday(NextDate) <> vbSaturday Then n = n + 1
        End Select
      End If
    Loop
    WorkDay = NextDate
  Else
    If holidays Then ' Count all days except holidays defined in tbl_holidays.
      n = 0
      Step = Sgn(days)
      NextDate = Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays)
      Do Until n >= Abs(days)
        NextDate = NextDate + Step
        HolidayMatchCount = DCount("holiday_date", "tbl_Holidays", "holiday_date = #" & Format(NextDate, "yyyy-mm-dd", vbMonday, vbFirstFourDays) & "#")
        If HolidayMatchCount < 1 Then n = n + 1
      Loop
      WorkDay = NextDate
    Else ' Count all days.
      WorkDay = DateAdd("d", days, Format(start_date, "yyyy-mm-dd", vbMonday, vbFirstFourDays))
    End If
  End If

Exit_Procedure:
  Exit Function

Err_Procedure:
  Dim strErrorMessage As String
  Select Case Err.Number
    Case 0  'Everything is fine :)
    Case Else
    strErrorMessage = "We got an unexpected error, please note these details:" & vbCrLf & _
                      "Error number: " & Err.Number & vbCrLf & _
                      "Error description: " & Err.Description & vbCrLf & _
                      "Error source: " & Err.Source & vbCrLf & _
                      "Session: " & Environ("computername") & "\" & Environ("username")
  End Select
  Debug.Print strErrorMessage
  Resume Exit_Procedure
End Function
Click to expand...
how do you call this
 
Minty

Minty

AWF VIP
Local time
Today, 07:39
Joined
Jul 26, 2013
Messages
10,366
@Number11 - Please this function and your question are in your other thread.
Posting multiple questions in multiple threads will cause you to get either conflicting responses or confused ones.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 07:39
Joined
Sep 21, 2011
Messages
14,232
Minty said:
@Number11 - Please this function and your question are in your other thread.
Posting multiple questions in multiple threads will cause you to get either conflicting responses or confused ones.
Click to expand...
Or none at all ?
 
N

Number11

Member
Local time
Today, 07:39
Joined
Jan 29, 2020
Messages
607
Minty said:
@Number11 - Please this function and your question are in your other thread.
Posting multiple questions in multiple threads will cause you to get either conflicting responses or confused ones.
Click to expand...
this is a different function and method so i am trying to find a solution thanks
 
You must log in or register to reply here.

Similar threads

smtazulislam
Attendance by days name of week and correspond date/Month
Replies
15
Views
561
MajP
MajP
X
How to import a CSV "String" into Access Recordset?
Replies
8
Views
530
Josef P.
J
A
Solved Code cannot differentiate between regional dates
Replies
5
Views
504
Ajabu
A
1
How to print all the records after each working day
2
Replies
29
Views
751
113hans
1
H
Date on Double Click
Replies
8
Views
160
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom