Subtracting business days (with holiday table) from a START DATE (1 Viewer)

[Speak_of]

here is the holiday table.

ID Name varchar HolidayDate Weekday varchar
1 New Year’s Day 1/2/2017 Monday
2 President’s Day 2/20/2017 Monday
3 Spring Holiday 4/14/2017 Friday
4 Memorial Day 5/29/2017 Monday
5 Independence Day 7/4/2017 Tuesday
6 Labor Day 9/4/2017 Monday
7 Thanksgiving 11/23/2017 Thursday
8 Thanksgiving 11/24/2017 Friday
9 Christmas 12/25/2017 Monday
10 New Year’s Day 1/1/2018 Monday
11 President’s Day 2/19/2018 Monday
12 Spring Holiday 3/30/2018 Friday
13 Memorial Day 5/28/2018 Monday
14 Independence Day 7/4/2018 Wednesday
15 Labor Day 9/3/2018 Monday
16 Thanksgiving 11/22/2018 Thursday
17 Thanksgiving 11/23/2018 Friday
18 Christmas 12/25/2018 Tuesday
19 New Year’s Day 1/1/2019 Tuesday
20 President's Day 2/18/2019 Monday
21 Spring Holiday 3/29/2019 Friday
22 Memorial Day 5/27/2019 Monday
23 Independence Day 7/4/2019 Thursday
24 Labor Day 9/2/2019 Monday
25 Thanksgiving 11/28/2019 Thursday
26 Thanksgiving 11/29/2019 Friday
27 Christmas 12/25/2019 Wednesday

 

[isladogs]

Thanks
Please test arnel's latest code & see if that will work - if necessary with a tweak or two

 

[Speak_of]

do you mean it is counting only upto 9? i chng it

Public Function SubtractWeekdays(dteStartDate As Date, lngNumOfDays As Long)
Dim lngCount As Long
Dim lngCtr As Long
Dim dteDate As Date
 
lngCount = lngNumOfDays + 1
lngCtr = -1
dteDate = dteStartDate
 
Debug.Print "Date", "Day Count", "Weekday"
Do
  dteDate = DateAdd("d", lngCtr, dteDate)
    Select Case Weekday(dteDate)
      Case 7, 1      'Saturday and Sunday, do nothing
      Case Else      'Monday thru Friday, OK
        'Is it a Holiday as posted in tblHolidays?
        If DCount("*", "tblHolidays", "[HolidayDate] = #" & Format(dteDate, "mm/dd/yyyy") & "#") < 1 Then    'NOT Holiday
            lngCount = lngCount - 1       'decrement Counter
            Debug.Print dteDate, lngCount, Weekday(dteDate)
        End If
    End Select
Loop While lngCount > 1
'* just to be sure, recheck our returning date
'* make sure it does not fall on weekend and holiday
While InStr("Sat/Sun", Format(dteDate, "ddd")) > 0 Or _
    DCount("*", "tblHolidays", "[HolidayDate] = #" & Format(dteDate, "mm/dd/yyyy") & "#") > 0
    '* advance the day by 1
    dteDate = dteDate + 1
Wend
SubtractWeekdays = dteDate
End Function

Thank you for trying to help Arnel. It doesn't work the same way. See the output.

Visit Start Visit End ConfrMemo_Due_Date
1/15/2019 1/16/2019 1/14/2019
1/22/2019 1/24/2019 1/21/2019
1/14/2019 1/17/2019 1/14/2019
1/29/2019 1/31/2019 1/28/2019
1/16/2019 1/17/2019 1/15/2019
1/15/2019 1/16/2019 1/14/2019
1/15/2019 1/17/2019 1/14/2019
1/8/2019 1/10/2019 1/7/2019
1/9/2019 1/10/2019 1/8/2019
1/15/2019 1/15/2019 1/14/2019

 

[Speak_of]

wait! WAIT! it works. I removed - from -10 and it works!! look!

Visit Start Visit End ConfrMemo_Due_Date
1/15/2019 1/16/2019 12/31/2018
1/22/2019 1/24/2019 1/8/2019
1/14/2019 1/17/2019 12/28/2018
1/29/2019 1/31/2019 1/15/2019
1/16/2019 1/17/2019 1/2/2019
1/15/2019 1/16/2019 12/31/2018
1/15/2019 1/17/2019 12/31/2018
1/8/2019 1/10/2019 12/21/2018
1/9/2019 1/10/2019 12/24/2018
1/15/2019 1/15/2019 12/31/2018

 

[Speak_of]

Can you PLEASE tell me why I have to use this ConfrMemo_Due_Date: SubtractWeekdays([Visit Start],10) in the query and not this ConfrMemo_Due_Date: SubtractWeekdays([Visit Start],-10)

THANK YOU! THANK YOU! THANK YOU!

 

[isladogs]

If that is now working, that excellent BUT ....
In post #17, you agreed that a start date of 1/15/2019 would result in a due date of 1/2/2019.
Where in post #24 where you said it works, the due date is shown as 12/31/2018.
One or the other due date is incorrect!

 

[Speak_of]

If that is now working, that excellent BUT ....
In post #17, you agreed that a start date of 1/15/2019 would result in a due date of 1/2/2019.
Where in post #24 where you said it works, the due date is shown as 12/31/2018.
One or the other due date is incorrect!

@isladogs

No everything is perfect! the 1st is the holiday, so it selects 31st, the output is 10 business days before start date.

 

[Speak_of]

@isladogs

Can you tell me why I have to use this ConfrMemo_Due_Date: SubtractWeekdays([Visit Start],10) in the query and not this ConfrMemo_Due_Date: SubtractWeekdays([Visit Start],-10)?

and what do i need to do properly say thank you here?

 

[isladogs]

Glad you have a solution after all that confusion.

UPDATE
Just realised your question wasn't related to arnel's code...
Your SubtractWeekdays will subtract 10 days if that's what you use as the second argument
If you use -10 then subtracting -10 is the same as adding 10.

As for your last question, suggest you read this thread https://www.access-programmers.co.uk/forums/showthread.php?t=296747.

You could also mark the thread as solved

 

[Speak_of]

THANK YOU Arnel this is SOVLED.

 

[Gasman]

THANK YOU Arnel this is SOVLED.

So mark it solved, via Thread Tools/Mark as solved?