Subtracting working days (and holidays) from a date

[ECEK]

I'm using the following function however I need it to work for subtracting days.
Any thoughts?

Function addWorkDays(addNumber As Long, Date2 As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim finalDate As Date
    Dim I As Long, tmpDate As Date
    tmpDate = Date2
    I = 1
    Do While I <= addNumber
        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And _
            DCount("*", "tbl_BankHolidays", "bankDate = " & CDbl(tmpDate)) = 0 Then I = I + 1
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop
    
    addWorkDays = tmpDate
End Function

 

[Isskint]

Hi

You just need to make number property of the DateAdd() function negative, so

DateAdd("d", -1, tmpDate)

So if you called the function as this - addWorkDays(5, 15/06/2016) it will return 09/06/2016 unless there were bank holidays

 

[ECEK]

Baldrick I Love you !!!

Like a dream. Many thanks.
I just renamed the function subWorkDays.

 

[ECEK]

Hi there Isskint: I tried this and presumed it to be ok however it just takes the number of day (2 in my case) off the date it doesn't account for weekends or holidays ??

Im also wondering what this line references ?

        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And _
            
End Function

Are these the weekdays to be excluded surely it should be 6 and 7 ?

 

[jdraw]

Do you have a table for Holidays?
Maybe you should post your database with an example.

 

[ECEK]

Yes I have a table for holidays (as illustrated in the code - tbl_BankHolidays)
This code works fine for adding days it just doesn't do it for subtraction.

Here is the adding code:

Function addWorkDays(addNumber As Long, Date2 As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim finalDate As Date
    Dim i As Long, tmpDate As Date
    tmpDate = Date2
    i = 1
    Do While i <= addNumber
        If Weekday(tmpDate) <> 6 And Weekday(tmpDate) <> 7 And _
            DCount("*", "tbl_BankHolidays", "bankDate = " & CDbl(tmpDate)) = 0 Then i = i + 1
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop
    
    addWorkDays = tmpDate
End Function

I just need to modify this to be able to subtract.

 

[arnelgp]

Function addWorkDays(addNumber As Long, Date2 As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim finalDate As Date
    Dim i As Long, tmpDate As Date
    tmpDate = Date2
    i = 1
    Do While i <= addNumber
        If Weekday(tmpDate) <> 6 And Weekday(tmpDate) <> 7 And _
            DCount("*", "tbl_BankHolidays", "bankDate = " & CDbl(tmpDate)) = 0 Then i = i + 1
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop
    
    addWorkDays = DateAdd("d", i-1, Date2)
End Function

 

[ECEK]

Im afraid that doesn't work Arnie !!

 

[jdraw]

Im afraid that doesn't work Arnie !!

What is it specifically that doesn't work?
Error message?

 

[arnelgp]

you will have to test for variant solution. i don't really know what you want to achieve:

addWorkDays = DateAdd("d", addNumber+i-1, Date2)

 

[ECEK]

Lets try this from scratch.

I want to subtract two days from a date.

I do not wish to count working days (ie weekends) or any dates that appear in my table of holiday dates.

 

[arnelgp]

Function addWorkDays(addNumber As Long, Date2 As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim finalDate As Date
    Dim i As Long, tmpDate As Date
    tmpDate = Date2
    i = 0: j = 0
    Do While i <= addNumber
        If Weekday(tmpDate) < 7 And _
            nz(DCount("*", "tbl_BankHolidays", "bankDate = " & CDbl(tmpDate)),0) = 0 Then i = i + 1
        tmpDate = DateAdd("d", -1, tmpDate)
    Loop
    
    addWorkDays = tmpDate
End Function

 

[ECEK]

OOOOoooooooooh Soooooo close !!!

Its nearly right. I tested it through a holiday period and it was two days short. I realised that there were two weekends during this period so Im wondering if this line of the code needs to change?

If Weekday(tmpDate) < 7 And

I've replaced this line with:

If Weekday(tmpDate) <> 6 And Weekday(tmpDate) <> 7 And

However it doesnt change the result.

Am I barking up the wrong tree?

So close...................

 

[arnelgp]

6 means friday, 7 means saturday is this your holiday?

 

[Gasman]

Hi there Isskint: I tried this and presumed it to be ok however it just takes the number of day (2 in my case) off the date it doesn't account for weekends or holidays ??

Im also wondering what this line references ?

        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And _
            
End Function

Are these the weekdays to be excluded surely it should be 6 and 7 ?

*Default* day number is 1 for Sunday and 6 for Saturday?

http://www.techonthenet.com/access/functions/date/weekday.php

 

[ECEK]

Ive been using 6 and 7 presuming (as God would have it...on the seventh day and all that !!!) that 6 was Saturday and 7 was Sunday. My whole code is out !!! Even on my add days !!!

Let me retest.

back later

 

[ECEK]

Still not right !!! Grrrr
We can do this !!!

If I add 2 days to a Friday Date then it workd.
If I add two days to a Thursday date then it ends up on a Saturday.
Ive attached a version of this.
As you will see. The minus date for the 3rd January works because of all of the Christmas holidays. But there should never be a weekend date in the results.

 

[jdraw]

There is a function and test routine to add/subtract business days here.

 

[arnelgp]

would you try:

 

[Gasman]

Ive been using 6 and 7 presuming (as God would have it...on the seventh day and all that !!!) that 6 was Saturday and 7 was Sunday. My whole code is out !!! Even on my add days !!!

Let me retest.

back later

That link show you how to set your start of the week if need be.
Personally I just used it as is, and allowed for it in my coding.