Populate Date Field 10 working Days from now and skip bank holidays (1 Viewer)

[Number11]

So I am looking for way for the from to populate the date field (Appointment Date) to be today's plus 10 working days, and check if that date is a Bank holiday if so offer next working day after bank holiday is this possible?

 

[namliam]

Its possible, best practice is to make either a workdays table or holidays table so you can reliably make the calculation
I.e. make a workdays table add a sequential id for each day.
Now its simple to find today, take the ID , add 10 and find the appropriate day.

 

[vba_php]

how much VBA coding experience do you have? you have to use the DATEADD() function along with another date function, possibly DATEPART(). you can do it any number of ways, but attached is from my github page. it calculates the number of business days between 2 dates. if you know how to code, you can change it to get what you need. you have to list the actual dates of the specific bank holidays somewhere so you can refer to them.

 

[plog]

This questions gets asked frequently here. Here's one post about it:

Calculate Business Days - No Holiday Table Needed (including MLK day)

Calculate Business Days - No Holiday Table Needed Public Function BusinessDays(dteStartDate As Date, dteEndDate As Date) As Long Dim lngYear As Long Dim lngEYear As Long Dim dteStart As Date, dteEnd As Date Dim dteCurr As Date Dim lngDay As Long Dim lngDiff As Long...

www.access-programmers.co.uk

I'm sure if you search using that threads title you will find even more help on the subject.

 

[Pat Hartman]

Here is a sample database I built that includes a holiday table and shows how to use the code.

 

[Number11]

Here is a sample database I built that includes a holiday table and shows how to use the code.

Many Thanks but what i am looking for is for my form to provide a date 10 plus in the future and if that day falls on a holiday skip to next date

 

[vba_php]

Many Thanks but what i am looking for is for my form to provide a date 10 plus in the future and if that day falls on a holiday skip to next date

Number, what have you tried so far? have you attempted it, or do you need someone to provide you with a complete solution?

 

[jdraw]

Number11,
Can you not adapt something from Pat's materials to meet your requirement? Perhaps you could tell us specifically what is the issue at this point.

Consider:
- a list of Holiday dates.
- review your table data and identify 10 working days out (X)
-is X a date in the Holiday dates?
If yes -do appropriate action (based on your rules)
If no - do the appropriate action(based on your rules).

 

[Number11]

Number, what have you tried so far? have you attempted it, or do you need someone to provide you with a complete solution?

So I have created a bank Holiday Table and can add 10 days to todays date using "DateAdd("ww",2,Date())" set as defualt in the appointmet date field, but ihave no idea how to then look at the holidays dates and skip it

 

[vba_php]

So I have created a bank Holiday Table and can add 10 days to todays date using "DateAdd("ww",2,Date())" set as defualt in the appointmet date field, but ihave no idea how to then look at the holidays dates and skip it

well if u have some coding skills u can modify the function in that zip I uploaded for u to do that. And by the way isn't the argument "ww" referring to weeks not days? I believe if u want to add days u have to use "d" or "dd" in that arg don't u? But at any rate I won't able to help ya for another ur or 2 as im at an appointment but hopefully the other guys can chime in before then.

 

[namliam]

How about checking out pat's solution?

 

[vba_php]

hey numbers,

if you're still looking for solution, this is how you do it, sir! have fun with it!

 

[Pat Hartman]

a date 10 plus in the future and if that day falls on a holiday skip to next date

So, it's OK to end on a weekend day? If so, just add 10 to the date using DateAdd() and check the result by looking into the holiday table. If it's a holiday, add 1 using DateAdd().