Calculate a date that is "X" days from a known Date Field and excludes weekends

[NickNandhra]

Hi,

I am working on a database which stores a start date, and a duration, the output required is when the sum start date and duration excl weekends i.e. if the startdate was the 30/10/13 and the duration is 7, the calculated date should equal 07/11/13.

I have attached a sample DB with fields / form.

Thanks in advance.

Nick.

[zmbd]

Basically you need to calculate business days, yes?
HowTo: Calculate business days - pure SQL approach.
Is one such method that does not involve VBA

Here are some thread here on Bytes.com along the same topic:
Google: Bytes.com "business days"

Then there's the goodole microsoft approach... this is for ACC 2007; however, it should work for ACC 2010: Counting the Number of Working Days in Access 2007

If these don't pan out let us know in detail why, post your code (formatted using the [CODE/] format) and/or the SQL (also with the [CODE/] format).

Also, please note: Most of the Experts may not be able to download attachments - especially if they are on file storage sites. This is due mainly to their company IT guidlines - or in my case, an actual block. It is prefered therefore, to have the question clearly stated, error messages posted in full, etc...

[neelsfer]

i use this code in a module, i got from the net and it is adapted to my start and end dates, to calculate weekdays, excluding weekends
It works fine for me
i call it in a query like this

WeekDays: Weekdays([OrderDate],[DateReceive])

Public Function Weekdays(ByRef OrderDate As Date, _
    ByRef receivedate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from orderdate
    ' to receivedate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If receivedate < OrderDate Then
        dtmX = receivedate
        OrderDate = receivedate
        receivedate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back orderdate).
    varDays = DateDiff(Interval:="d", _
        date1:=OrderDate, _
        date2:=receivedate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=OrderDate, _
        date2:=receivedate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=OrderDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=receivedate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit

[NickNandhra]

Thanks for the replies, the solutions posted both use a 'start date' and a known 'finish date'. The fields I am working with is just the 'start date' and 'duration'. I'm not sure how to adapt the code above so that it does what I need it t.

I will try to explain my requirements:

The fields in the db are:
ProgrammedStart Date - this is the start date (format dd/mm/yy)
ProgrammedDurat ion - this is the number of business days which need to be added to the above (& excludes Saturdays and Sundays / holidays).
(both of the above are entered by the user)

CalFinishDate - is the output I need help with and stored in the same table. If I was using Excel, I would use the =WORKDAY.INTL(A 1,7,1) formula (where A1 = 30/10/13) will produce 08/11/13. It is this which I need in to reproduce in Access - however unable to with my limited knowledge.

[zmbd]

What I would do is:
1) using the dateadd() function add the duration to my start date.
2) using one of the methods given in my or Neelsfer's posting, use the start date and the calculated date from (1) as the initial input to the methods.

2a) if the number of days returned is the same as your duration then you are done.
2b) if the number of days returned are less than your duration, add the difference to the date calculated in (1) and repeat (2) until (2a) is true.

3) Return the results

3a) start date as given in the data set
3b) end data as calculated from (2)