Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Working days formula

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Working days formula

Resolved · High Priority · Version 2003

Reply Wed 19 Aug 2009, 15:40Delegate James said...

James has attended:
Access VBA course

Working days formula

calculating working days diff rather than daysdiff.
steve asked to post reminder

For upcoming training course dates see: Pricing & availability

Reply Wed 26 Aug 2009, 11:59Trainer Jacob said...

RE: working days formula

Hi James

Thanks for the post, Stephen is available tomorrow and will review your post.

regards

Jacob

Reply Fri 11 Sep 2009, 17:01Delegate James said...

RE: working days formula

Still no reply to my question

Unless I am missing it?

Reply Mon 26 Oct 2009, 15:25 Edited on Mon 26 Oct 2009, 15:26Trainer Stephen said...

RE: working days formula

Hi James

Apologies, I was sure I had answered this but I can find no reference to it, so I can only imagine that there was some glitch when I tried to post.

The following function will count the working days between two dates, by excluding Saturdays and Sundays.

Function WorkDays(dteStart As Date, dteFinish As Date) As Integer Dim dteDateCount As Date Dim intCount As Integer For dteDateCount = dteStart To dteFinish If Weekday(dteDateCount) <> 7 Then 'excludes saturdays If Weekday(dteDateCount) <> 1 Then 'excludes sundays intCount = intCount + 1 End If End If Next dteDateCount WorkDays = intCount End Function

It uses the vb function weekdays to exclude dates on a saturday (day7) or a sunday(day1).

The following procedure calls this function and places two dates for the start and finish arguments

Sub Caller() MsgBox WorkDays(#10/19/2009#, #10/28/2009#) End sub

Hope this helps

Regards

Stephen

Mon 2 Nov 2009: Automatically marked as resolved.

Access articles

· Interested In The Golden Rules For Building Access Databases?
· How much time could you save with Access?
· Microsoft Access Flaws and Ways to Overcome Them
· How To Import Data Easily Into Access 2010
· Access Database Optimization: First Normal Form

Please browse our web site to find out more about
access database microsoft training and other Microsoft training courses.

Access tip:

Closing form after a certain time period

To make a form close automatically after a certain time period, you need to assign the close function to a macro.

Save the macro and ope up the form in design view. open the Properties sheet.

In the TimerInterval property enter the length of time you want the form open for. This should be in milliseconds, so for instance if you want the form open for 5 seconds enter 5000, for an minute enter 60000.

You now need to attach your macro (to execute the Close action) to the OnTimer event property of the form.

View all Access hints and tips