Calculate working days

[graeme101]

I have to calculate the number of working days between two dates in a query. I can calculate the number of days just by deducting the start date from the end date but i need some way to deduct the number of days that fall on a weekend. I know there is a weekend function which returns a number relating to which day of the week it is but i am not sure how to use this to get the result i need.

Any help or advice much appreciated.

Cheers

Graeme

 

[raskew]

You might try calling this function from a calculated field in your query.

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17") As Integer
'*****************************************************
'Name:      CalcWkDays2 (Function)
'Purpose:   Count # of days between two dates, with
'           options to:
'           (1) Include or exclude the start date in count
'           (2) Specify weekdays to exclude (default
'               set to Saturday (7) & Sunday (1).  To exclude
'               Tuesday (3) & Thursday (5), specify "35".
'Parameters:    dteStartDate & dteEndDate formatted as dates
'               YCnt:   Specify True to include start date in
'                       count, False to exclude it.
'               pExcl:  Weekdays to exclude
'Inputs:    From debug window:
'           (1) ? CalcWkDays2(#01/01/01#, #07/01/01#, True)
'           (2) ? CalcWkDays2(#01/01/01#, #07/01/01#, False)
'           (3) ? CalcWkDays2(#01/01/01#, #07/01/01#, True, "")
'           (4) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"")
'           (5) ? CalcWkDays2(#07/01/01#, #01/01/01#, False,"")
'           (6) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"23456")
'Output:    (1) 130; (2) 129; (3) 182; (4) 181; (5) -181; (6) 52
'
'*****************************************************

Dim n As Integer, wdays As String, datehold As Date, dteFlag As Boolean

dteFlag = False
'Reverse the dates if they were input backwards
If dteStartDate > dteEndDate Then
   datehold = dteStartDate
   dteStartDate = dteEndDate
   dteEndDate = datehold
   dteFlag = True
End If
n = 0
dteStartDate = dteStartDate - Not (YCnt)
'days to exclude (7 & 1 unless other specified)
wdays = pExcl

Do While dteStartDate <= dteEndDate
   n = n + IIf(InStr(wdays, WeekDay(dteStartDate)) = 0, 1, 0)
   dteStartDate = dteStartDate + 1
Loop
'return negative value if the Start Date
'was initially greater than the End Date
CalcWkDays2 = n * IIf(dteFlag, -1, 1)
End Function

 

[graeme101]

OK.
Do I write the function as a module?
And
How do I call the function from the query?

Graeme

 

[raskew]

Here's an example of how you might employ this function.

(1) Copy the function from this forum.

(2) Go to your Northwind database, open a new module and paste the function to it.
Name and save the module (the module name makes no difference, so long as it is unique).

(3) Copy the following query-sql.

(4) Return to Northwind, create a new query and paste the query-sql to it.

(5) Run the query. What you'll see in ProcDays is the number of days between
[OrderDate] and [ShippedDate], excluding any intervening Saturdays and/or Sundays.

(6) Experiment with the query in design-view to get an idea of what it's doing.

HTH

Bob

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate, CalcWkDays2([OrderDate],[ShippedDate],False) AS ProcDays
FROM Orders;

 

[MattS]

I encountered a similar problem (calculating working days) when designing a Absence/Holiday database, but encountered problems with National Holidays (i.e. Xmas Day - obviously not a working day but could fall on any day of the week).

Any ideas on how the above function could be amended to take these into account?

Thanks,

Matt.

 

[ColinEssex]

Have a look at this site. Its got some downloads about date calculations including taking bank holidays into account.

Col

http://www.mvps.org/access/resources/downloads.htm#D

 

[MattS]

Many thanks Colin. Have amended some of the code from that site to suit my needs and it works like a dream.

Thanks again,

Matt.

 

[maricha]

Hello, I have input this into my database and it works in the qry. However I can not get it to show me TAT <=5, between 6 and 20, between 21-30. Any help on using this code and getting these results? Thank You for any help.

Here's an example of how you might employ this function.

(1) Copy the function from this forum.

(2) Go to your Northwind database, open a new module and paste the function to it.
Name and save the module (the module name makes no difference, so long as it is unique).

(3) Copy the following query-sql.

(4) Return to Northwind, create a new query and paste the query-sql to it.

(5) Run the query. What you'll see in ProcDays is the number of days between
[OrderDate] and [ShippedDate], excluding any intervening Saturdays and/or Sundays.

(6) Experiment with the query in design-view to get an idea of what it's doing.

HTH

Bob

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate, CalcWkDays2([OrderDate],[ShippedDate],False) AS ProcDays
FROM Orders;

 

[redneckgeek]

Hello, I have input this into my database and it works in the qry. However I can not get it to show me TAT <=5, between 6 and 20, between 21-30. Any help on using this code and getting these results? Thank You for any help.

Care to elaborate a little? I don't see "TAT" mentioned anywhere else in this thread.

 

[raskew]

However I can not get it to show me TAT <=5, between 6 and 20, between 21-30.

TAT must be coming from your code. You'll need to show us what you're doing.

Bob