Add months to a date so result falls on a work day

[vivlet]

I am working in a query and have struggled to solve this problem.

I have a date "ClockStart " and I want to add 2, 3 and 6 months to this date in separate fields but the result must be on a work day NOT a weekend. For the straight addition of months I know I can use DateAdd:

DateAdd("m",2,[ClockStart])

I have a module code that I have used when adding DAYS to a date which results on a work day but don't know how to amend (if possible) for MONTHS:

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)
    Else
        dtTest = DateAdd("d", sngAdjustDy, dtTest)
    End If
Case 7
    If sngAdjustDy < 0 Then
        dtTest = DateAdd("d", sngAdjustDy, dtTest)
    Else
        dtTest = DateAdd("d", sngAdjustDy + sngAdjustDy, dtTest)
    End If
End Select
    funReturnWkdy = dtTest
    
End Function

Expression used in query:

funReturnWkdy([ClockStart],4)

An example of dates that fall on weekends are:

ClockStart 03/12/2012 (Monday)
2 months: 03/02/2012 (Sunday)
3 months: 03/02/2012 (Sunday)

Hope you can help.

[NeoPa]

The following code allows you to add a parameter to your call that matshes the DateAdd() Interval parameter :

Public Function funReturnWkdy(dtStartDte As Date, _
                              intToAdd As Integer, _
                              Optional strType As String = "d") 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
    Dim intDay As Integer

    dtTest = DateAdd(strType, intToAdd, dtStartDte)
    intDay = Weekday(dtTest, vbMonday)
    If intDay > 5 Then
        If intToAdd < 0 Then
            dtTest = DateAdd("d", intDay - 5, dtTest)
        Else
            dtTest = DateAdd("d", 8 - intDay, dtTest)
        End If
    End If
    funReturnWkdy = dtTest
End Function

The call would be :

funReturnWkdy([ClockStart],2,'m')

[vivlet]

That's brilliant - Thank you!

I have noticed though with the code I'm using that the result always takes me forward to the next work day even if the result lands on a Saturday, where it should take me back to the Friday before (if it lands on a Saturday) - can you see where it is going awry?

Thanks in advance.

[NeoPa]

Yes Viv. It's what the code does. It's written in specially to do exactly that as that's what the original routine did. If you want it to go forward or backwards depending on the day it lands on rather than the direction it's moving in (as it is currently written), then the code would be :

Public Function funReturnWkdy(dtStartDte As Date, _
                              intToAdd As Integer, _
                              Optional strType As String = "d") 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
    Dim intDay As Integer

    dtTest = DateAdd(strType, intToAdd, dtStartDte)
    intDay = Weekday(dtTest, vbMonday)
    If intDay > 5 Then dtTest = DateAdd("d", IIf(intDay = 6, -1, 1), dtTest)
    funReturnWkdy = dtTest
End Function

NB. I haven't amended any comments. I'll leave that for you to manage.