How to calculate number of workdays in access2013 query

[ESAKKI109]

In the attached image I was trying to calculate number of working days(throughput) b/w item received_date and item_released date by using datadiff with "w" interval . but if I use "w" interval it brings value zero .but if I use "d" interval then it brings result with inclusive of Saturdays and Sundays.i don't need that.

so can you help me to calculate the number of working days exclusive of Saturdays and sundays.

[twinnyfo]

ESAKKI,

It might be more feasible to write a small VBA function that you can call, sending the start and stop dates. The function would start at the start date, and add a day. If that date is not a Saturday or Sunday, then have a counter that increments by one. Once the function increments the date to the end date, it exits, returning the value of the counter.

Very rough concept here:

Public Function WorkDays(StartDate As Date, StopDate As Date) As Integer
    Dim intCounter As Integer
    Dim dtHold As Date
    intCounter = 0
    dtHold = StartDate
    Do While Not dtHold > StopDate
        If Weekday(dtHold, vbSunday) > 1 And _
            Weekday(dtHold, vbSunday) < 7 Then
                intCounter = intCounter + 1
        End If
        dtHold = DateAdd("d", 1, dtHold)
    Loop
    WorkDays = intCounter
End Function

But, thinking about it, it just might work as is....

As an example,

WorkDays (#6/1/2014#, #6/7/2014#)

Returns 5.

[Seth Schrock]

Check out the following link: DateDiff for Business Days. This particular solution excludes holidays, Saturdays and Sundays. If you don't care about holidays, then you could leave that part out and just look at how it excludes the weekend.

[zmbd]

In addition to the information in that thread there is also our insight's article for those with a prefernce for SQL:
How To Calculate Business days, A Pure SQL approach

[ESAKKI109]

Hi Twinnyfo ,

thanks for the vba coding . I am not very much familiar with vba coding .but I understood the concept of your coding . my doubt is ?
Is this coding for Microsoft access2013 or MS excel ? because I don't know how to implement this code in MS Access2013 . where do need to implement these whether in MS access form or queries ??.
In my case (refer screenshot) what need is(exact wordings) throughput = RECEIVED_DATE - ITEM_RLEASED DATE . Do I need to change any wordings in your coding according to my convenience . Also where I need to paste these coding in MS access query.

[zmbd]

ESAKKI109
I'll be forwarding a copy of my basic tutorial boiler plate.
You need to work thru the examples and take a look at the other reference links.
As much as we like to help, there are certain fundamentals that you must simply have mastery of, such as the Access UI, in order for you to get the most out of our help.

>>edit>> Sent, Please Check your Bytes.com inbox (^_^)

[twinnyfo]

Esakki,

You can use the VBA I posted just about anywhere within your project, as it is a public function. It just depends on how and when you want to get the number of workdays. You can also use the Function as part of the query you have displayed. It can be one of your output fields.