Excluding weekends and bank holidays

thedeadzeds · Oct 14, 2015

Hi Guys,

Is there a way of auto populating a date based on another date whilst excluding weekends and bank holidays?

So with regards to the attached database, can date due in frm_1 show 1 WORKING day ahead of the date input (eg date input is 14/10/15 so date due should show 15/10/15). This obviously gets tricky on a Friday (or bank holidays) as I don’t want the date to then show a Saturday for example?

Hope this makes sense?

Regards

plog · Oct 14, 2015

Yes, Microsoft has an article that describes this:

https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx?f=255&MSPPError=-2147217396

arnelgp · Oct 14, 2015

here try a made another table tblHoliday, you should fill this table with actual holidays in your area.

alwazeer · Oct 31, 2015

You are awesome arnelgp
How can i use Two conditions in the same code

I try But I got error (Duplicate declaration in current scope)

Code:

 Select Case B_date
 Case 1
    Const strDateToIgnore As String = "Fri/Sat"
  Case 2
    Const strDateToIgnore As String = "Fri/Sat/mon/wed"
   
   End Select

Thanks

arnelgp · Oct 31, 2015

then don't use the constant variable

Dim strDateToIgnore as string
Select Case B_date Case 1 strDateToIgnore = "Fri/Sat" Case 2 strDateToIgnore = "Fri/Sat/mon/wed" End Select

alwazeer · Oct 31, 2015

Hi arnelgp

I fill the tblHoliday with actual holidays in My area, Put the code didn't count it

its value is zero!!
Why??

Thanks a gain

arnelgp · Oct 31, 2015

from where are you calling this function. its supposed to skip those dates, and in your case statement.

alwazeer · Oct 31, 2015

arnelgp said:
from where are you calling this function. its supposed to skip those dates, and in your case statement.

Thanks For your Replay
i put (4) holidays in The table tblHoliday

I call it form the txtInputDate after update

But when enter it doesn't skip those dates

arnelgp · Oct 31, 2015

is it possible for you to upload your db?

alwazeer · Oct 31, 2015

The same attach that u r upload

her is it

i fill Holidays but when go to frm_1 the code work normal without skip the Holidays

arnelgp · Oct 31, 2015

here try this:

alwazeer · Oct 31, 2015

Thanks very much
It Works
I have question:
How can i count Friday and Saturday in the month??

Excluding weekends and bank holidays

thedeadzeds

Registered User.

Attachments

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

Attachments

alwazeer

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

alwazeer

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

alwazeer

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

alwazeer

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

alwazeer

Registered User.

Similar threads

Users who are viewing this thread