RTHEREALRDJ
Technical User
I need to calculate the working days between a sent and received date on access. I need a way that bank holidays and weekends are automaticaly removed. I have a bank holiday table on the database.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HOW TO REMOVE WEEKENDS AND BANK HOLIDAYS WHEN RUNNING DATEDIFF FUNCTIO
- Thread starter RTHEREALRDJ
- Start date
- Status
Firstly, this is the VBA I use to calculate how many workdays there are between two dates:
hollength = 0
For temp_date = start_date To end_date
If Weekday(temp_date) <> vbSaturday And Weekday(temp_date) <> vbSunday Then
hollength = hollength + 1
End If
Next temp_date
Where hollength returns the number of workdays between start_date and end_date.
Secondly, you are quite right in thinking that you should have a seperate table for bank holidays. Once this has been set up, all you need to do is run another loop and for each iteration of the loop check to see whether any of the days between the start date and end date are in the bank holidays table.
Hope this helps!
hollength = 0
For temp_date = start_date To end_date
If Weekday(temp_date) <> vbSaturday And Weekday(temp_date) <> vbSunday Then
hollength = hollength + 1
End If
Next temp_date
Where hollength returns the number of workdays between start_date and end_date.
Secondly, you are quite right in thinking that you should have a seperate table for bank holidays. Once this has been set up, all you need to do is run another loop and for each iteration of the loop check to see whether any of the days between the start date and end date are in the bank holidays table.
Hope this helps!
- Thread starter
- #3
RTHEREALRDJ
Technical User
Thank you for this help.
I have tried running this code and the loop but have not had much success. The two dates are simply labelled "Sent Date" and "Returned Date" and the table "bank Holiday" but I can not get it to operate.
I have tried running this code and the loop but have not had much success. The two dates are simply labelled "Sent Date" and "Returned Date" and the table "bank Holiday" but I can not get it to operate.
MichaelRed
Programmer
faq181-261
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question
