dateadd function returns weekends even when weekday "w" is specified

Posted on 2003-03-31
Medium Priority
Last Modified: 2008-11-01
dateadd function returns weekends even when weekday "w" is specified . Example, day_1: DateAdd("w",-1,Histmf_8002_02_1!date)
returns Sunday 10/21/2001 when given Monday 10/22/2001 as date. day_1: DateAdd("w",1,Histmf_8002_02_1!date) returns Saturday 10/20/2001 when given 10/19/2001 as date.

Is there a fix or workaround?
Question by:gopal3
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Expert Comment

by:Brendt Hess
ID: 8242369
"w" (weekday) means Day of the Week, not day of the Work Week, or non-weekend day.  

To get the work week days (Mon -- Fri), you will have to do some additional coding, e.g.:

iif(WeekDay(Dateadd("d", [Days], Histmf_8002_02_1!date)) = 7, iif(Sgn([Days])>=0, Dateadd("d", [Days]+2, Histmf_8002_02_1!date), Dateadd("d",[Days]-1, Histmf_8002_02_1!date)), iif(WeekDay(Dateadd("d", [Days], Histmf_8002_02_1!date)) = 1, iif(Sgn([Days])>=0, Dateadd("d", [Days]+1, Histmf_8002_02_1!date), Dateadd("d",[Days]-2, Histmf_8002_02_1!date)), Dateadd("d", [Days], Histmf_8002_02_1!date))

This, of course, is not a full solution.  If you wanted to go 30 working days out, you would need to adjust for multiple weekends between the start date and the end date.

Accepted Solution

RemRemRem earned 300 total points
ID: 8243435
Create the following function in a module. Call the function passing the date you want to evaluate plus a positive or negative number you want to add or subtract to the date passed.  If you pass a negative number and it lands on a Sat/Sun, it assumes you want to go back to the prior Friday. If you pass a positive number, it assumes you want to go forward to the next Monday.

-Rachel Morris
M.G.C.D. Consulting

Public Function funReturnWkdy(dtStartDte As Date, sngNumDysToAdd As Single) As Date
'   Note - sngNumDysToAdd can be positive or negative
'   Function assumes that if you are passing a reduction, aka a negative
'   sngNumDysToAdd, you want to return to the prior Friday if
'   date passed equals Saturday or Sunday, and if you pass a positive
'   sngNumDysToAdd, you want to move forward to the next Monday

Dim dtTest As Date, sngAdjustDy As Single
If sngNumDysToAdd < 0 Then sngAdjustDy = -1 Else sngAdjustDy = 1

dtTest = DateAdd("d", sngNumDysToAdd, dtStartDte)

Select Case Weekday(dtTest)
Case 1  '   Sunday
    If sngAdjustDy < 0 Then
        dtTest = DateAdd("d", sngAdjustDy + sngAdjustDy, dtTest)
        dtTest = DateAdd("d", sngAdjustDy, dtTest)
    End If
Case 7
    If sngAdjustDy < 0 Then
        dtTest = DateAdd("d", sngAdjustDy, dtTest)
        dtTest = DateAdd("d", sngAdjustDy + sngAdjustDy, dtTest)
    End If
End Select
    funReturnWkdy = dtTest
End Function
LVL 28

Expert Comment

ID: 8675572
No comment has been added lately (68 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to RemRemRem http:#8243435

Please leave any comments here within 7 days.



EE Cleanup Volunteer
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post a request in Community support (with a link to this page) to refund your points. http://www.experts-exchange.com/Community_Support/
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 22860325
this function is wrong!

add 4 working days to Thurs, should give Wed.
The function only accounts for end date LANDING on a weekend.

see http://support.microsoft.com/kb/115489

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question