# Date Add problem (calendar days vs working days) (1 Viewer)

#### bdhtexas

##### Registered User.
I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

Can anyone help, thanks for your time!!!

#### llkhoutx

##### Registered User.
Working days is necessarily straight forward, especially when holdays are taken into account.

I've seen several functions for calculating work days. Try searching here and with Google.

#### bdhtexas

##### Registered User.
What if I have a Working Day Table that lists only the Working Days and no holidays?

How would I code the query to show only the records that are 15 working days or more from today?

#### llkhoutx

##### Registered User.
You should query your working days table. I assume that it's full of dates, without weekends, but with holidays. Your formula is the correct method.

It's tough not having holidays. You must be at UTMB.

##### AWF VIP
Hi -

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

Your formula is the correct method.

Quote #1 seems to be correct. Here's an example from the debug (immediate) window (A97), US short/date format.

Code:
``````? DateAdd("w",-12,Date()) 'date = 15-Dec-06
12/3/06

? DateAdd("d",-12, Date()) 'date = 15-Dec-06
12/3/06``````

If you don't believe it, try it for yourself and publish back your results and rationale.

Please correct me if I'm wrong, but there appears to be no difference between specifying "d" or "w" in the DateAdd() function. The more accurate answer, assuming Saturday and Sunday as non-workdays is #11/29/06#. The fact that there is a table that lists no weekend dates doesn't seem to be a factor, particularly if we're attempting to use the DateAdd() function to count back from a specific date.

Thanks - Bob

Last edited:

#### llkhoutx

##### Registered User.
I've got a bunch of date formulas. I'll look through them.

I think that you've wasted your time building a date tabe with only workdays. That cann all be calculated.

I'll get back with you.

##### AWF VIP
Hello,

I think that you've wasted your time building a date tabe with only workdays. That cann all be calculated.

The issue is not about a user's table but rather an Access function that doesn't appear to work as advertised.

If I'm messed up here, please post back a working solution.

Bob

##### AWF VIP
Hi -

The following is not a one-liner, but it should get you
back n business days (Monday - Friday). Copy/paste
to a module and call it as shown.

Code:
``````Function BackBusDays2(pStart As Date, _
pnum As Integer) As Date
'*******************************************
'purpose:   Output date if pNum business
'           days (Mon - Fri) are subtracted
'           from pStart.
'Inputs:    from debug (immediate) window
'           ? BackBusDays2(#2/22/06#, 3)
'Output:    2/17/06
'*******************************************

Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer

dteHold = pStart - 1
n = 0
Do While n < pnum
i = WeekDay(dteHold)
n = n + IIf(i = 1 Or i = 7, 0, 1)
dteHold = dteHold - 1
Loop

BackBusDays2 = dteHold + 1
End Function``````

Bob

Last edited:

#### fretwhizz

##### New member
Just worked out how to do this myself. This code requires a table called bank holidays with a field called [Date]. It seems to work ok.

Public Function wdateadd(Startdate As Date, wdays As Integer)
Dim h As Integer
Dim w As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = Currentdb
Set rst = DB.OpenRecordset("SELECT [Date] FROM tblBankHoliday", dbOpenSnapshot)
w = 0
h = 0
Do Until h = wdays
rst.FindFirst "[Date] = #" & mydate & "#"
If Weekday(mydate) <> vbSunday And Weekday(mydate) <> vbSaturday Then
If rst.NoMatch Then
h = h + 1
End If
End If
w = w + 1
Loop

End Function

Replies
14
Views
243
Replies
6
Views
172
Replies
7
Views
188
Replies
5
Views
166
Replies
5
Views
285