Calculate Working Days

[Carrie]

I am using Access 2002. I am fairly new to Access. I can do simple queries but nothing real major.

I have analysts have have calls open in their queue. I need to know how old their calls are using 9 hour business days. So if a call was in my queue and the call was opened on 01/14/05 and today is 02/03/05. Excluding weekends and MLK day which was a holiday is would have been open for 13 Days.

How can I get it to calculate the amount of working days a call has been open?

Ultimately, I will need to group these into categories (i.e. Carrie has 3 calls that are 0-3 days old and 5 calls that are 4-7 days old). I just figured I would start with the first part.

I hope this has been helpful.

 

[tails]

It is quite possible to do, but it takes a lil bit of coding. About the public holidays, i dont think that it is possible to exclude public holidays from the list. I'm not sure on the code itself, but from what i don know i dont think you will be able to uninclude public holidays, but weekends should be possible.

 

[Andrew Fergus]

Hi,

The following SQL for a select query will calcuate the number of work days between a start date and today (excluding today). Please note that this formula does not take account of holidays.

I used the table name T_Dates and the one variable that I used from that table was [start] - be sure to use your own variable and table names. I defined a number of variables within the query - namely Today, Elapsed, XSDays, XSWDays (the nub of the formula), Weeks and Workdays. You may combine some of these variables if you choose or elect not to display some of the variables.

SELECT T_Dates.start, Weekday(Now()) AS Today, Int(Now()-[start]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[start])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates;

HTH, Andrew.

 

[mdmilner]

You should also consider looking at this site.

http://www.mvps.org/access/datetime/index.html

Mike