Report Formula - datediff weekdays

Question

I have the following formula in a report field

 =(DateDiff("d",[datesubmitted],[datecompleted]))

I only want this to calculate the actual difference between weekdays, I am not concerned with working days ie public holidays.  I have tried using "w" and "ww" and these only count the number of weeks.  Is it possible to only count only the weekdays.

I feel like this should be a simple request, however I have just about been on every forum and help site and there does not seem to be such a simple solution!  Any assistance would be much appreciated.

Thankyou in adavance.

Answer 1

Jack

One thing I'd like to point out...If this is called by a query with lots of rows it would be better to set a object with a public scope once and then just keep it open. That would speed up performance.

Answer 2

There is nothing stopping you from using Excel functions in Access.  Here is an example for calling the Excel NetWorkDays function.

Public Function ExcelNetWorkDays(dFirstDate As Date, dSecondDate As Date)

    On Error GoTo Err_Proc

    Dim oApp As Object

    Set oApp = CreateObject("Excel.Application")

    ExcelNetWorkDays = oApp.NetworkDays(dFirstDate, dSecondDate)

Exit_Proc:

    oApp.Quit

    Set oApp = Nothing

    Exit Function

Err_Proc:

    If Err.Number = 429 Then

        MsgBox "MS Excel not found...aborting!", vbInformation, "Error"

        Resume Exit_Proc

    Else

        MsgBox Err.Number & " - " & Err.Description

        Resume Exit_Proc

    End If

End Function

Answer 3

Ken's function will do the job, but I just wanted to deal with your complaint. While Excel does have a built in NetWorkDays function, Access doesn't. What you need to remember is that Access is much more of a development platform than any other Office app. So, there are numerous common functions and processes that there are no built in functions or commands for. But one can easily add those functions. I am a bit surprised that you were unable to find a NetWorkDays function as there are numerous examples of them on the Web. 

My point is to not expect Access to do everything for you. Some things need to be done by yourself or with the help of many others who have gone before you and already created these functions.

Answer 4

Things can be as simple as possible, but no more so.  Take a look at the file Workdays.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This includes a number of functions for returning the difference in 'working days' between two dates, giving the option of allowing for public holidays or not.  You'll probably want the WorkDaysDiff_SingleCountry function

Another simple way is to create an auxiliary calendar table of working days.  You'll find the means of doing so in Calendar.zip in the same SkyDrive folder.  It's then just a case of counting the days between the two dates, which you can do by calling the DCount function.