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

[Speak_of]

Good morning/afternoon,

I have a module that calculates future dates from a start date and I need to figure out how to add subtraction part to it. So, the same idea, start date minus 10 business days (calling is in a query), if falls on a holiday, then the next business day is the DATE. I tried several things, it just doesn’t work. I am not a programmer, just self-taught… I am working in 2016 Access. The code is below. Any help will be much appreciated!!

Public Function AddWeekdays(dteStartDate As Date, lngNumOfDays As Long)
Dim lngCount As Long
Dim lngCtr As Long
Dim dteDate As Date
 
lngCount = 0
lngCtr = 1
 
Debug.Print "Date", "Day Count", "Weekday"
 
Do
  dteDate = DateAdd("d", lngCtr, dteStartDate)
    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] = #" & dteDate & "#") < 1 Then     'NOT Holiday
          lngCount = lngCount + 1       'Increment Counter
            Debug.Print dteDate, lngCount, Weekday(dteDate)
        End If
    End Select
    lngCtr = lngCtr + 1
Loop While lngCount < lngNumOfDays
  AddWeekdays = dteDate
End Function

 

[isladogs]

Hi & welcome to AWF
For some reason your post was moderated. That sometimes happens with new members.
I've approved your post & at the same time added code tags to make it easier to read.

however, I'm not absolutely clear what your question is.
Perhaps a bit more detail would help

Your Case 7,1 seems superfluous

Could just use Case Is 2 To 6 ???

 

[Speak_of]

Thank you, isladogs. What other detail can i add to make it clear?

 

[isladogs]

I have a module that calculates future dates from a start date and I need to figure out how to add subtraction part to it. So, the same idea, start date minus 10 business days (calling is in a query), if falls on a holiday, then the next business day is the DATE.

I'm not clear how that fits in with your code or indeed why you're looping through

Do you only need to add 1 extra day if the calculated end date falls on a holiday OR are you saying you need 10 working days from your start date?
And why subtraction when you are adding 10 days?

Perhaps it would help to add a few simple date examples - you can presumably allow for Xmas & New Year holidays depending on your country of origin

StartDate EndDate
19/12/2018 ?
20/12/2018 ?
21/12/2018 ?
24/12/2018 ?

 

[arnelgp]

Public Function AddWeekdays(dteStartDate As Date, lngNumOfDays As Long)
Dim lngCount As Long
Dim lngCtr As Long
Dim dteDate As Date
 
lngCount = 0
lngCtr = 1
 
Debug.Print "Date", "Day Count", "Weekday"
 
Do
  dteDate = DateAdd("d", lngCtr, dteStartDate)
    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       'Increment Counter
            Debug.Print dteDate, lngCount, Weekday(dteDate)
        End If
    End Select
    lngCtr = lngCtr + 1
Loop While lngCount < lngNumOfDays
  AddWeekdays = dteDate
End Function

 

[isladogs]

Hi arnel
Isn't that the same as the OP's code from post #1?

 

[arnelgp]

yes with format.

 

[Speak_of]

I need to have both, adding and subtracting. Everything works fine with the adding part. It works like this; the START DATE is entered in to the main table via form. This date generates deliverable dates (END DATES) after the START DATE and one deliverable date 10 business days prior to START DATE. if the END DATE falls on a holiday (the function checks the table of holidays), then the END DATE should be the next business day(s).

Example:

START DATE (16JAN2019) – 10 days prior is 01JAN2019 (holiday), the END DATE should be 02JAN2019

Does this help?

 

[isladogs]

@Speak_of
My confusion is partly the concept of having an END DATE 10 working days BEFORE a START DATE !!!!! Also that your code ADDS rather than SUBTRACTS!

Your example didn't help! I'm using dd/mm/yyyy syntax below...
16/01/2019 is a WED. 10 business days prior to that is 02/01/2019 (WED)

How about
15/01/2019 (TUE). 10 business days prior to that is 01/01/2019 (TUE) but that's a holiday so use 02/01/2019 instead

What about 08/01/2019? 10 business days prior to that is 25/12/2018 (TUE) but that's a holiday. Those 10 days include holidays on 26/12/2018 and 01/01/2019 so what date do you want there?

As you have a table of calendar dates with holidays marked, have you considered scrapping the loop code and doing the following:
a) subtract 10 working days - actually 14 calendar days - I'll call it dteCalc
b) then use something similar to the following to allow for holidays where needed
DMin("CalendarDate", "tblCalendar","CalendarDate>= #" & dteCalc & "# And Holiday = False")

Anyway, does arnel's date formatting (which I didn't notice before) solve your problem?

 

[Speak_of]

my mistake. the START DATE should have been 15JAN2018. All of my start dates are about one month+ into the future. does this help with confusion?

 

[Speak_of]

can addition and subtraction be in the same code? maybe it should be separate module?

 

[isladogs]

As you didn't answer my 3 questions from the last post, it doesn't help with confusion.
Yes you can do your subtraction in the same code as any addition needed to adjust the date.
It seems to me that you understand what you want but you've not been able to explain it to me so I can't offer any more suggestions than I already have.

Good luck

 

[arnelgp]

not fully checked yet.

Public Function SubtractWeekdays(dteStartDate As Date, lngNumOfDays As Long)
Dim lngCount As Long
Dim lngCtr As Long
Dim dteDate As Date
 
lngCount = lngNumOfDays
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
  SubtractWeekdays = dteDate
End Function

 

[Speak_of]

Listen, i am not trying to be disrespectful. you are losing me with so many questions. I told you I am not a programmer and I was trying to get your focus only on the problem that doesn't work. it's my form of efficiency, since I am occupying your time. Can we try again? I really do need help. What do you need to know?

Thank you, Julie

 

[Speak_of]

hi Arney, it doesn't work. subtracting one day only. The query is calling for -10.

 

[isladogs]

Assuming that is aimed at me, nor am I trying to be difficult.
But without answers from you I really can't help you (& I doubt anyone else will do much better)

I'll try asking the questions from post #9 again
However only one point (3b) requires any programming knowledge.

1. Please explain the concept of having an END DATE 10 working days BEFORE a START DATE

2. I asked for specific examples to clarify the logic
We've now agreed the answer for 16/01/2019 was indeed 02/01/2019.
a) How about 15/01/2019 (TUE). 10 business days prior to that is 01/01/2019 (TUE) but that's a holiday so does that also result in using 02/01/2019.

b) What about 08/01/2019? 10 business days prior to that is 25/12/2018 (TUE) but that's a holiday. Those 10 days include holidays on 26/12/2018 and 01/01/2019 so what date do you want there?

3. As you have a table of calendar dates with holidays marked, have you considered scrapping the loop code and doing the following:
a) subtract 10 working days - actually 14 calendar days - I'll call it dteCalc
b) then use something similar to the following to allow for holidays where needed
DMin("CalendarDate", "tblCalendar","CalendarDate>= #" & dteCalc & "# And Holiday = False")
That's the only bit of code in my previous post!
It means get the first (minimum) date in the calendar starting with the calculated date from part a) where that date isn't a holiday

4. Does arnel's code which includes date formatting solve your problem?
He's now provided two versions for you to try out.
The first one adds days as in your original code
The second one subtracts them

EDIT: This crossed your last post - you've just answered question 4

 

[Speak_of]

@isladogs

Ok. Let’s see. My comments are in bold

1. Please explain the concept of having an END DATE 10 working days BEFORE a START DATE

START DATE is always at least a months in the future form now. A team member enters a START DATE into the Dd and the date generates several deliverables, one is 10 business days before the START DATE.

2. I asked for specific examples to clarify the logic
We've now agreed the answer for 16/01/2019 was indeed 02/01/2019.
a) How about 15/01/2019 (TUE). 10 business days prior to that is 01/01/2019 (TUE) but that's a holiday so does that also result in using 02/01/2019.

YES! Both should result in using 02JAN2019

b) What about 08/01/2019? 10 business days prior to that is 25/12/2018 (TUE) but that's a holiday. Those 10 days include holidays on 26/12/2018 and 01/01/2019 so what date do you want there?

The code has to create a due date 10 business days before the START DATE. It looks in the HOLIDAY table to find days to exclude from business day category. So any date in that table will push the DUE DATE further back. In the scenario 01JAN2018, the due date will be 26JAN2019. 26th is not a holiday in US.

3. As you have a table of calendar dates with holidays marked, have you considered scrapping the loop code and doing the following:
a) subtract 10 working days - actually 14 calendar days - I'll call it dteCalc
b) then use something similar to the following to allow for holidays where needed
DMin("CalendarDate", "tblCalendar","CalendarDate>+ #" & dteCalc & "# And Holiday = False")
That's the only bit of code in my previous post!
It means get the first (minimum) date in the calendar starting with the calculated date from part a) where that date isn't a holiday

NO. Everything was working fine until I was asked to create the -10 day action item, which is now giving me a heartburn... I am working with people who always have afterthought.

4. Does arnel's code which includes date formatting solve your problem?
He's now provided two versions for you to try out.
The first one adds days as in your original code
The second one subtracts them

I only tried the one that subtracts. It subtracts 1 day. The query is asking for -10.

 

[isladogs]

I'll reply properly tomorrow but:

2b) As 26 Dec isn't a holiday in the US, then I think you meant the due date should be 26 Dec 2018 (not 26 Jan 2019) from a start date of 8 Jan. Correct?
Really not sure what you meant by "In the scenario 01JAN2018, the due date will be 26JAN2019"

3b) All projects always include changes following afterthoughts.
Sometimes that involves a change in code

Arnel may well post a third variation on your original code overnight
I'm thinking of a different approach if he doesn't provide the solution first.

The formula I posted had a typo (since corrected), It should have been:

DMin("CalendarDate", "tblCalendar","CalendarDate>[B][COLOR="Red"]=[/COLOR][/B] #" & dteCalc & "# And Holiday = False")

In order to test that idea tomorrow, please could you post your table of holiday dates e.g. as an Excel file or better upload part of your ACCDB file

 

[Speak_of]

@isladogs

My comments are in bold

2b) As 26 Dec isn't a holiday in the US, then I think you meant the due date should be 26 Dec 2018 (not 26 Jan 2019) from a start date of 8 Jan. Correct?
Really not sure what you meant by "In the scenario 01JAN2018, the due date will be 26JAN2019"

That’s right. Typo

3b) All projects always include changes following afterthoughts.
Sometimes that involves a change in code.

I gather that so far. I will send the table of holidays when I get in tomorrow (US) morning

Thank you

 

[arnelgp]

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