View Single Post
Old March 30th, 2008, 03:40 PM
dbartelt dbartelt is offline
Authorized User
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default DateDiff to exclude weekends and holidays


I am working on an On Time Delivery Report that is generated on a monthly basis. The form specifies a single customer name and the date fields are From: and To: Ie. From 12/01/07 to 12/31/07. I need to count workdays only. Weekends and Holidays need to be excluded. There can be as many as 100 orders for a specific customer any given month. Each order appears on a separate line on the report.

I placed the following code in the On Format field of the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim WorkDays As String

WorkDays = DateDiff("d", Me.JobEntryDateText, Me.DDate)

Me.Days.Value = WorkDays

End Sub

"d" gives me the total days (counting the weekends and holidays) example - 12/21/06 to 01/03/07 = 13 days

"w" gives me the number of full 7 day weeks - example 12/21/06 to 01/03/07 = 1

Any help will be appreciated! Thanks

D. Bartelt
D. Bartelt