DateDiff function

[JenM]

Hi. I am a novice user. I am using the datediff function in a query. I am trying to calculate the number of week days between two dates. When I use "m" as the interval, the number of weeks is returned. When I use "d" or "y" as the interval, the number of days is returned. What kind of calculation can I add to the statement to only count the number of week days? Is this possible?

I was thinking I might have to run a query on a query and put in a formula that somehow subtracts 2 days from every 7. The next problem would be if there were only 6 days. Then I would only want it to subtract 1 day.

thanks in advance for your help.

[missinglinq]

An interval of “m” should give you months, not weeks! The following site has 2 different functions for calculating week/work days, one that does just that and a more complicated one that allows for the exclusion of certain holidays.

Calculating Work/Week Days

Welcome to TheScripts!

Linq ;0)>

[JenM]

An interval of “m” should give you months, not weeks! The following site has 2 different functions for calculating week/work days, one that does just that and a more complicated one that allows for the exclusion of certain holidays.

Calculating Work/Week Days

Welcome to TheScripts!

Linq ;0)>

Thank you. Yes, I did mean "w" as the interval. Where/how do I enter the code in a query builder?

[Denburt]

The link he provided you with should do what you need. You would place the code into a module in VBA then in the query you can use something to define the field name and it would look something like this:

NumberOfWeekDays:WorkingDays(StartDate, EndDate)

This should give you the results you need let us know how it goes.

[JenM]

The link he provided you with should do what you need. You would place the code into a module in VBA then in the query you can use something to define the field name and it would look something like this:

NumberOfWeekDays:WorkingDays(StartDate, EndDate)

This should give you the results you need let us know how it goes.

Thank you for your help. I apologize for having trouble with something that must be simple. I'm not sure how to insert/run the module in the query. I created a module and used your suggestion for the field name in the query, but I received the following error "undefined function 'WorkingDays' in expression."

[martin DH]

I'd like to piggyback on this post if I could.

I'm trying to display the DateDiff result in a textbox on a report; my starting date is a field [SubmittedDate] and my ending date is Now() - I want it to calc the number of weekdays between the submitted date and whenever the report is ran.
I used the module posted (WorkingDays), changed its starting and ending dates to reflect my fields, created a textbox on my report with a control source of the WorkingDays function. When I run the report I get parameter boxes asking for "WorkingDays", "SubmittedDate", and "Now()" and then "Error" in the textbox on the completed report.

Advice?

[Denburt]

Thank you for your help. I apologize for having trouble with something that must be simple. I'm not sure how to insert/run the module in the query. I created a module and used your suggestion for the field name in the query, but I received the following error "undefined function 'WorkingDays' in expression."

Sorry it has been so long for me to get back with yall on this I have been out for a while.

Jen this may or may not be what you r looking for. I took a random table from a DB I have called Basis and it has a field name called Delivery_Month this is what the SQL would look like.

SELECT WorkingDays([Delivery_Month],Now()) AS NumberOfWeekDays
FROM Basis;

My Basis table has a field in it called Delivery_Month and it is defined as a date field. This will give me the number of working days between the date in the Delivery_Month field and now or today. BTW you could also use date() since Now() provides the time as well and that really isn't needed.

[Denburt]

Martin, I have no idea why you are having the problem you are having I have used the same table that I used in my last post added the field Delivery_Month to the report. Then in another Text Box I added the following and it seems to work just fine. =WorkingDays([Delivery_Month],Now()) of course again I would use date() just because it is a function designed for returning the date only. The function Now() also returns the time and may cause issues depending on the circumstances.

=WorkingDays([Delivery_Month],Date())