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

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
"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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Robberbaron (robr)Commented:
this function is wrong!

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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.