Solved Check If Date is a Bank Holiday (1 Viewer)

Number11

Member
Local time
Today, 09:34
Joined
Jan 29, 2020
Messages
607
So i have this code that creates a date 12 working days from today, but i now need for it to check if the date its offering as a Bank Holiday and if so move to the next weking day.

Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
Dim i As Integer
Dim m As Integer
m = Abs(iDays)
While m > 0
If iDays < 0 Then
dteStart = dteStart - 1
Else
dteStart = dteStart + 1
End If
If InStr(1, "sat/sun", Format$(dteStart, "ddd")) <> 0 Then
Else
m = m - 1
End If

Wend
addDays = dteStart
End Function


End Function

the above is called via form default value within date fkield as

=addDays(+12,Date())

I have all then dates in a table called "BankHolidays"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:34
Joined
May 7, 2009
Messages
19,169
replace [yourDateField] with the name of your date field.
Code:
Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
    Dim m As Integer, dte As Date
    m = Abs(iDays)
    dte = dteStart
    While m > 0
        dte = DateAdd("d", 1, dte)
        If DCount("1", "BankHolidays", "[yourDateField] = #" & Format$(dte, "mm/dd/yyyy") & "#") = 0 Then
            If InStr(1, "sat/sun", Format$(dte, "ddd")) = 0 Then
                m = m - 1
            End If
        End If
    Wend
    addDays = dte
End Function
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:34
Joined
Sep 12, 2006
Messages
15,613
So i have this code that creates a date 12 working days from today, but i now need for it to check if the date its offering as a Bank Holiday and if so move to the next weking day.

but you probably need to check each day.

Take a date like April 8th, 2022 12 days hence is April 20th

April 9 and April 10 are weekend days
April 16 and 17th are weekend days
April 15th is Good Friday
April 18th is Easter Monday.

So you have 6 extra days to add on, so April 20th becomes April 26th
But now you have another weekend, April 23rd and 24th

So maybe you end up with April 28th

@arnelgp routine should do this, but you need to set up a bank holiday table, and decide how you want to treat weekend days.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:34
Joined
Feb 28, 2001
Messages
26,996
I'm with Dave on this one. You are in the UK so I have to presume that your bank holidays are handled similarly to the USA bank holidays. We have an "actual" holiday but we often have an "observed" holiday to match it. For instance, "President's Day" honored the birthday of George Washington, but then was modified by Congress to honor all presidents. The actual date, 22 Jan, is still on calendars, but federal institutions (which includes banks) observe the day on the Monday that precedes the actual holiday if it is a weekday or FOLLOWS the actual holiday if it is on a Saturday or Sunday. (Or something complex like that...) So the EASIEST way to know which day is the holiday is to have a table of known holidays to take into account the OBSERVED dates. This means that the year WILL be part of the date because the dates change from year to year.

Which means your function is actually in two parts. FIRST, compute the date as though there were no holidays. Just do your DateAdd. BUT once you have a tentative date, do a DCount of that date in the holiday table. If the returned count is 0, you are good to go. Otherwise, you need to do a further adjustment.
 

Number11

Member
Local time
Today, 09:34
Joined
Jan 29, 2020
Messages
607
replace [yourDateField] with the name of your date field.
Code:
Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
    Dim m As Integer, dte As Date
    m = Abs(iDays)
    dte = dteStart
    While m > 0
        dte = DateAdd("d", 1, dte)
        If DCount("1", "BankHolidays", "[yourDateField] = #" & Format$(dte, "mm/dd/yyyy") & "#") = 0 Then
            If InStr(1, "sat/sun", Format$(dte, "ddd")) = 0 Then
                m = m - 1
            End If
        End If
    Wend
    addDays = dte
End Function
thanks this all worked and i have tested :)
 

Number11

Member
Local time
Today, 09:34
Joined
Jan 29, 2020
Messages
607
but you probably need to check each day.

Take a date like April 8th, 2022 12 days hence is April 20th

April 9 and April 10 are weekend days
April 16 and 17th are weekend days
April 15th is Good Friday
April 18th is Easter Monday.

So you have 6 extra days to add on, so April 20th becomes April 26th
But now you have another weekend, April 23rd and 24th

So maybe you end up with April 28th

@arnelgp routine should do this, but you need to set up a bank holiday table, and decide how you want to treat weekend days.
thanks i had already setup a table and it all works fine :)
"I have all then dates in a table called "BankHolidays""
 

Users who are viewing this thread

Top Bottom