DateAdd Function

[scgoodman]

Please help...new to access. However I understand the function at hand,but cannot get it to work. I need to add working days between these dates. So I used the 'w' function in the below statement, however it is working the same as 'd'. HELP!!

Here is my function below:


EDL HP PO Due Date: IIf([VENDOR]=Yes,DateAdd("w",[tbl_Turnaround Schedule]![Turnaround Time],[ExFactory Date]),IIf([tbl_Master Data Table]![HP Business Unit]="ESS",DateAdd("w",4,[ExFactory Date]),DateAdd("w",5,[ExFactory Date])))

 

[MSAccessRookie]

Please help...new to access. However I understand the function at hand,but cannot get it to work. I need to add working days between these dates. So I used the 'w' function in the below statement, however it is working the same as 'd'. HELP!!

Here is my function below:


EDL HP PO Due Date: IIf([VENDOR]=Yes,DateAdd("w",[tbl_Turnaround Schedule]![Turnaround Time],[ExFactory Date]),IIf([tbl_Master Data Table]![HP Business Unit]="ESS",DateAdd("w",4,[ExFactory Date]),DateAdd("w",5,[ExFactory Date])))

The reason that it does not work the way you expect, is because a "w" in the type field represents weeks. I recommend that you search the Forums for "working days". When you do, you will get a number of links regarding this type of issue, including the one below, which may be a good start for you.

http://www.access-programmers.co.uk/forums/showthread.php?t=156930&highlight=working+days

 

[raskew]

Hi -

The 'w' (weekday) option can be a bit deceptive. It equates to days, not work (business) days. To add business days (Mon - Fri), try saving this to a module, then calling as shown:

Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer

    dteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold), 1)
       Else          'subtract days
          dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay(dteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = dteHold
    
End Function

HTH - Bob

 

[raskew]

Hi again -

The reason that it does not work the way you expect, is because a "w" in the type field represents weeks.

Just a clarification. "w" represents weekdays, which in this context includes all weekdays (Sun. - Sat). "ww" represents weeks.

Using the DateAdd() function with "d", "w" or "y" all return the same result, as can be seen from these examples from the immediate window:

? dateadd("d", 7, date())
11/6/2008 
? dateadd("w", 7, date())
11/6/2008 
? dateadd("y", 7, date())
11/6/2008

...as opposed to the "ww" option, which adds weeks:

? dateadd("ww", 7, date())
12/18/2008

Bob

 

[scgoodman]

I used both 'd', and 'w' and they came back the same. So if I have a day (1) Sunday and I want to return (5) workdays (business days) then I would do a DateAdd("w", 5, Date()).

Please help!
Shannon

 

[boblarson]

You might find this enlightening (as should everyone else):

http://www.utteraccess.com/forums/s...8288&page=0&view=collapsed&sb=5&o=365&fpart=1

 

[raskew]

Hi Bob -

That is indeed enlightening, provided readers realize that DateAdd() and DateDiff() handle 'w' differently! Thanks Microsoft for that!

Shannon -

Have you been reading the responses? Your example, adding five days/work days/etc to Sunday, e.g.

? dateadd("w", 5, #11/2/08#)
11/7/2008

...appears to work correctly. However, try starting on Monday and see what happens.

? dateadd("w", 5, #11/3/08#)
11/8/2008

11/8/2008 is a Saturday, not a working/business day.

Now, copy/paste the function I provided above to a standard module, then, from the debug (immediate) window:

? upbusdays3(#11/3/08#, 5, true)
11/10/2008

Bottom line - DateAdd("w", ... adds days, not working/business days. There is no built-in function that adds working/business days. Thus, it requires a User Defined Function (UDF).

Bob