Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1791
  • Last Modified:

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?
1 Solution
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
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/
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.

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now