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

bdhtexas

Registered User.
Local time
Today, 01:21
Joined
Dec 3, 2003
Messages
79
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.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!
 

llkhoutx

Registered User.
Local time
Today, 01:21
Joined
Feb 26, 2001
Messages
4,018
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.
Local time
Today, 01:21
Joined
Dec 3, 2003
Messages
79
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.
Local time
Today, 01:21
Joined
Feb 26, 2001
Messages
4,018
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.
 

raskew

AWF VIP
Local time
Today, 01:21
Joined
Jun 2, 2001
Messages
2,734
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.

Please help me out on this.

Thanks - Bob
 
Last edited:

llkhoutx

Registered User.
Local time
Today, 01:21
Joined
Feb 26, 2001
Messages
4,018
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.
 

raskew

AWF VIP
Local time
Today, 01:21
Joined
Jun 2, 2001
Messages
2,734
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
 

raskew

AWF VIP
Local time
Today, 01:21
Joined
Jun 2, 2001
Messages
2,734
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.
'coded by:  raskew
'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
Local time
Today, 06:21
Joined
Oct 13, 2010
Messages
1
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
mydate = DateAdd("d", w, Startdate)
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

wdateadd = DateAdd("d", w, Startdate)

End Function
 

Number11

Member
Local time
Today, 06:21
Joined
Jan 29, 2020
Messages
607
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.
how do you then call this to populate the date within a form field please?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:21
Joined
Sep 21, 2011
Messages
14,048
how do you then call this to populate the date within a form field please?
You pass the required values to the function?
in fact from your other thread, you just replace DateAdd() with this function? :unsure:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2002
Messages
42,981
Here's a sample database with a number of useful date functions with code that shows how to use them.

 

Users who are viewing this thread

Top Bottom