# of working days between receipt & complete

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a newbie trying to create the # of working days either in a query or via
a report between the date something was received & the date we finished it.
Does something exist for this?

Thanks in advance for any assistance. (P.S. I know nothing about VBA)
 
CaribbeanBlue said:
I'm a newbie trying to create the # of working days either in a query or via
a report between the date something was received & the date we finished it.
Does something exist for this?

Thanks in advance for any assistance. (P.S. I know nothing about VBA)
Click to expand...

There are a few "pre-fab" solutions for this. Many involve VBA. There is
also an Excel library function that might work for you. Try googling
"access working days" or "access networkdays", especially in google
groups for more on these.

Around here however these solutions are subjects of debate. There is no
way for these functions to know /your/ work schedule, especially when it
comes to excluded holidays and other exceptions specific to your
organization.

The most generic solution I know of is to create a separate table to
list out all possible working days (within practical limits) in your
organization. But how to build this table? Who wants to key thousands of
dates? If it were me, I would do the work in Excel. It's pretty easy to
generate a long, ordered list of dates excluding, say, all weekends.
Then go pick out and delete the holidays and other non-work days. Resort
the whole thing so the data rows are contiguous. Add a column that
sequentially numbers the dates from zero to whatever (doesn't matter
where you start numbering as long as it's sequential from one row to the
next). Import this Excel worksheet to a new table in Access. Your new
table looks something like

tblWorkDays
======================
DateIndex WorkDay
----------------------
0 2007/02/28
1 2007/03/01
2 2007/03/02
3 2007/03/05 <-- weekend dates skipped
4 2007/03/06
etc.

Now how to use this... You simply look up the DateIndex values for the
receive and finish dates and subtract. The DLookup function will
probably be quite useful here.

DLookup ("DateIndex", "tblWorkDays", "WorkDay = " & FinishDate) -
DLookup ("DateIndex", "tblWorkDays", "WorkDay = " & ReceiveDate)

If we pass FinishDate = #2007/03/05# and ReceiveDate = #2007/02/28# to
the expression above, we should get
3 -
0
equals 3 work days elapsed.

Hope this helps!
 
Back
Top