Calculate a date using x number of 'working days' from a date.

[mhschof]

I have an assigned SHIP DATE. I need to calculate an expected DUE date which is 9 working days prior to the ship date, and a START DATE which is 16 working days prior to the SHIP DATE. Working days are MON thru FRI. I am not concerned with eliminating holidays at this point but it would be nice, if not too complicated to do that at some point. I see many responses on how to calculate working days between two dates, but nothing on how to calculate a date based on x number of working days prior to a date.

[zmbd]

I really do not see any easy method here.
The logic one would need:
M - F, 5 days
So one would need to determine if more than 5 days between the days then determine if there are non-weekdays and account for those...

So take today 2015-06-23
Go back 9 days... so if we take 5 into 9 we get 1.8, or 7 days or one week... we need to pull off the workdays out of the week, so 5 * 1 week... gives us 5 days.... 9 less the 5 leaves us 4 days to step thru...

So take 2015-06-23, subtract the 7 days to give us 2015-06-16... now loop backwards a day (15th) Monday (4-1 = 3), loop back, Sunday... we can skip that day and the next so sub 2 gives us the next Friday the 12th (3-1 =2) loop the next two days out leaves us with a start day of 2015-06-10

So now that we have the logic worked out (and that took me awhile to figure) we can write our function


Caution this is air code... meaning I have not checked this in the VBA-Editor. I'm following the logic above and building as I go... The function would be called in the Query or via VBA etc as..

>>One Note: I don't usually provide the code upfront; however, I can see where this would have a great deal of value for many people... and I might be able to use it too.

FindDueDate([Date_value_or_f ield],days_to_back_c alc)
...
FindDueDate(#06/23/2015#,9)

Public Function FindDueDate(zDateIn as Date, zBackDays as Integer)
Dim zdueDate as Date
Dim zWeekCount as Long
Dim zDaysLeft as Long
Dim zCounter as Integer
'
on error goto z_error
'
zDueDate = zdatein
zCounter = zBackDays
'
'so find out how many 7 day weeks there are
zWeekCount = fix(zBackDays/5)
'
'and then subtract those days from the datein
If zWeekCount>0 then
   zDueDate = zDueDate - (zWeekCount*7)
endif
'
'and we need to find out how many days were accounted for
zDaysLeft = ZCounter - (zWeekCount *5)
zcounter = 0
'
'now loop thru the remaining days to see if there are any weekends
for zcounter = 1 to zDaysLeft
'
'if the day is a Sunday then skip it and Saturday
   if weekday(zDueDate) = 1 then
      zDueDate = zDueDate -2
   else
'otherwise just back up a day...
      zDueDate = zDueDate -1
   end if
next zcounter
'
'now return the date calc'd
FindDueDate = zDueDate
z_resume:
Exit Function
'
z_error:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "FncFindDueDate"
  Resume z_resume
end Function

Once again... it's very late here and I have not checked the above for syntax/logic errors - it's midnight my time and I'm running on fumes.
I already see where this could be built on to handle forward calcs too,...., I leave that to someone else for tonight.

[jforbes]

That's a cool function Zmbd. I've been using this one provided Microsoft: https://support.microsoft.com/en-us/kb/115489

   '**********************************************************
   'Declarations section of the module
   '**********************************************************
      Option Explicit

   '==========================================================
   ' The DateAddW() function provides a workday substitute
   ' for DateAdd("w", number, date). This function performs
   ' error checking and ignores fractional Interval values.
   '==========================================================
   Function DateAddW (ByVal TheDate, ByVal Interval)

     Dim Weeks As Long, OddDays As Long, Temp As String

     If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
                VarType(Interval)  > 5 Then
        DateAddW = TheDate
     ElseIf Interval = 0 Then
        DateAddW = TheDate
     ElseIf Interval > 0 Then
        Interval = Int(Interval)

   ' Make sure TheDate is a workday (round down).
        Temp = Format(TheDate, "ddd")
        If Temp = "Sun" Then
           TheDate = TheDate - 2
        ElseIf Temp = "Sat" Then
           TheDate = TheDate - 1
        End If

   ' Calculate Weeks and OddDays.
        Weeks = Int(Interval / 5)
        OddDays = Interval - (Weeks * 5)
        TheDate = TheDate + (Weeks * 7)

   ' Take OddDays weekend into account.
        If (DatePart("w", TheDate) + OddDays) > 6 Then
           TheDate = TheDate + OddDays + 2
        Else
           TheDate = TheDate + OddDays
        End If

        DateAddW = TheDate
     Else                         ' Interval is < 0
        Interval = Int(-Interval) ' Make positive & subtract later.

   ' Make sure TheDate is a workday (round up).
        Temp = Format(TheDate, "ddd")
        If Temp = "Sun" Then
           TheDate = TheDate + 1
        ElseIf Temp = "Sat" Then
           TheDate = TheDate + 2
        End If

   ' Calculate Weeks and OddDays.
        Weeks = Int(Interval / 5)
        OddDays = Interval - (Weeks * 5)
        TheDate = TheDate - (Weeks * 7)

   ' Take OddDays weekend into account.
        If (DatePart("w", TheDate) - OddDays) < 2 Then
           TheDate = TheDate - OddDays - 2
        Else
           TheDate = TheDate - OddDays
        End If

        DateAddW = TheDate
      End If

   End Function

I reposted their code for grins.

[zmbd]

Figures that the wheel would already be invented that's what I get for checking things before bed!

( rotfl )

[zmbd]

This ended up in my Inbox for some reason:

mhschof wrote:

zmbd has responded to your question on Bytes.com:

Calculate a date using x number of 'working days' from a date.

Reminder: please mark select "choose as best answer" for the reply that solves your question.

*************** **
This is an automated message, do not reply

Found this and it worked perfectly. I have users inputting a "Request Date". Upon entering a date, I need
Access to populate a second field ("Due Date"). When they enter their
Request Date, I want Access to set the default value of Due Date =
Request Date + 9 Business Days (Holidays don't matter. Just want
Saturday and Sunday taken out). Any thoughts?


Assuming [Request Date] is on a business day, add two weeks, i.e., 10
business days. Then subtract one day unless that day is a Monday where
three days are subtracted. Note that adding 14 days does not change
the Weekday value.

DateAdd('d', IIf(Weekday([Request Date]) <> 2, -1, -3), DateAdd('d',
14, [Request Date]))

If [Request Date] can fall on a weekend, Saturdays subtract two days
and Sundays subtract three days.

DateAdd('d', IIf(Weekday([Request Date]) < 3, -3, IIf(Weekday([Request
Date]) = 7, -2, -1)), DateAdd('d', 14, [Request Date]))

[zmbd]

@mhschof:

1+ Those functions may work; however, I think that you will be stifled with their approach. There's also a bit if checking you have to do to make sure you are using the correct set of calculations whereas either MS' function shown by JForbes or My function simply account for the weekend regardless of the date.

2+ Your original question asked for a back calculation, now it seems that you have changed your mind. MS function will already account for this and you can always hardcode the lead days. My function can be modified to calculate in either direction.

You need to decide what you want.

3+ Using the function that either MS or I have supplied along with the concept of using a table containing the holidays will allow you to implement the holiday checking. You will have a more difficult time doing this with the date functions you've proposed.

[NeoPa]

I offer this in case it can be helpful :

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (Weekday(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

It's not optimised but is reliable ;-)

[zmbd]

I had forgotten about the sgn() and of course the mod() is an easier method. I'll have to go in and make some changes to my code!
Very interesting that there are so many of us with little calcs to do this, and there's one in the Excel via standard addin, yet not one for Access...

[NeoPa]

DateAdd() actually has an Interval parameter that can take the value 'w' to indicate move along x number of weekdays.

Embedded in the Help page description are the following two snippets :

DateAdd():
To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

DateAdd():
Note
When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.

The latter is from Access 2010. This was not highlighted previously.

[jforbes]

The DateAdd() function with "w" - Weekday interval doesn't always work, atleast for me it didn't. That's what I tried a while back and instead ended up implementing the DateAddW() function provided by DateAdd() "w" Interval Does Not Work as Expected. If I remember right, it worked for me in Access 2013 and not Access 2007. I didn't try Access 2010.

[NeoPa]

I point you back at my earlier post Jeff :-D

Read it carefully all through.

Then have a laugh ;-)

[jforbes]

Oh, HaHa, whoever wrote that should be a politician.