Calculating Weekdays in a query

[T Best]

Hi all,

I'm sure this has probably been addressed before but here goes...i'm trying
to use the datediff function to return the number of days between two dates
but i only want to include weekdays. if i use DateDiff("w", [Start
Date],[End Date]) like Access help says, i get the number of weeks. This is
a known problem with Access 97. I know how to work around it with code but
is there anyway to do this in a query?

TIA
Ted

 

[Gary Walter]

I'm sure this has probably been addressed before but here goes...i'm
trying
to use the datediff function to return the number of days between two
dates
but i only want to include weekdays. if i use DateDiff("w", [Start
Date],[End Date]) like Access help says, i get the number of weeks. This
is
a known problem with Access 97. I know how to work around it with code but
is there anyway to do this in a query?

you probably have already found this,
but just in case....

ACC2000: How to Find Number of Working Days Between Two Dates
http://support.microsoft.com/default.aspx?scid=kb;en-us;210562

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288194

 

[Guest]

I was looking at both the ways to calculate work days and my question is
where do you put these and once placed how do you call them up, is it in the
criteria or first line in a query?

Thank you in advance for any help

I'm sure this has probably been addressed before but here goes...i'm
trying
to use the datediff function to return the number of days between two
dates
but i only want to include weekdays. if i use DateDiff("w", [Start
Date],[End Date]) like Access help says, i get the number of weeks. This
is
a known problem with Access 97. I know how to work around it with code but
is there anyway to do this in a query?

you probably have already found this,
but just in case....

ACC2000: How to Find Number of Working Days Between Two Dates
http://support.microsoft.com/default.aspx?scid=kb;en-us;210562

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288194

 

[Gary Walter]

A typical scenario is you have a table
of projects that have a startdate and
enddate, and you wish to compute the
number of working days it took to complete
the projects in a query.

tblProjects

ProjectID StartDate EndDate
1 1/1/2006 4/12/2006
2 2/1/2006 5/10/2006

save the 2 modules found here in a module:

http://www.mvps.org/access/datetime/date0006.htm

if you don't care about holidays, you can use the function
WorkingDays in a simple SELECT query:

SELECT
ProjectID,
StartDate,
EndDate,
WorkingDays([StartDate],[EndDate]) As WDays
FROM
tblProject;

returning:

ProjectID StartDate EndDate WDays
1 1/1/2006 4/12/2006 73
2 2/1/2006 5/10/2006 70

or, you can return only the projects that
took greater than 70 days to complete:

SELECT
ProjectID,
StartDate,
EndDate,
WorkingDays([StartDate],[EndDate]) As WDays
FROM
tblProject
WHERE
WorkingDays([StartDate],[EndDate]) > 70;

ProjectID StartDate EndDate WDays
1 1/1/2006 4/12/2006 73

Is the question where do you put the code?

Start a new module.
In the Database Window (w/Objects
listed down the left side), click on "Modules"
then "New" in windows upper left corner.

This will open up a blank "Microsoft Visual Basic"
window.

Under "Option Compare Database", type in

Option Explicit

Copy code from web page above and paste
into this module under "Option Explicit.".
(copy only text from "Code Start" to "Code End")

Save the module (say as "modDates") by clicking
on the "floppy icon" or selecting File/Save from
top menu.

To make sure haven't done something wrong,
click on "Debug/Compile ...." in top menu.

If everything went well, you are now ready to use
the functions in a query as illustrated above...

If you want to take holidays into account,
create a table named "tblHolidays" with a field
named "HolidayDate" and enter dates applicable
to your country/region/company.

Then you can use the WorkingDays2 function in
your queries.

BTW, Helen Feddema has also looked at workdays/holidays
"from every angle" in her Archon column:

http://www.woodyswatch.com/access/archtemplate.asp?3-24

(which includes a downloadable example mdb)

I was looking at both the ways to calculate work days and my question is
where do you put these and once placed how do you call them up, is it in the
criteria or first line in a query?

Thank you in advance for any help

I'm sure this has probably been addressed before but here goes...i'm
trying
to use the datediff function to return the number of days between two
dates
but i only want to include weekdays. if i use DateDiff("w", [Start
Date],[End Date]) like Access help says, i get the number of weeks. This
is
a known problem with Access 97. I know how to work around it with code but
is there anyway to do this in a query?

you probably have already found this,
but just in case....

ACC2000: How to Find Number of Working Days Between Two Dates
http://support.microsoft.com/default.aspx?scid=kb;en-us;210562

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288194

 

[Jamie Collins]

Helen Feddema has also looked at workdays/holidays
"from every angle" in her Archon column:

http://www.woodyswatch.com/access/archtemplate.asp?3-24

She briefly mentions a table of holidays but doesn't seem to mention
the standard SQL trick of a Calendar table of all dates with a column
to indicate which are holidays/workdays. It is a more common need to
know the workdays than the holidays e.g. see the title of this thread.
Having each workday in a row in an auxiliary table means it can be
joined to other temporal tables, a significant leap from merely knowing
the holiday dates.

Jamie.

--

 

[Gary Walter]

Good point Jamie..

You should email her.

She is always open to better mousetraps
(ideas) and always gives credit in her
columns when she shares these ...

 

[Guest]

That worked!

Thank you!

A typical scenario is you have a table
of projects that have a startdate and
enddate, and you wish to compute the
number of working days it took to complete
the projects in a query.

tblProjects

ProjectID StartDate EndDate
1 1/1/2006 4/12/2006
2 2/1/2006 5/10/2006

save the 2 modules found here in a module:

http://www.mvps.org/access/datetime/date0006.htm

if you don't care about holidays, you can use the function
WorkingDays in a simple SELECT query:

SELECT
ProjectID,
StartDate,
EndDate,
WorkingDays([StartDate],[EndDate]) As WDays
FROM
tblProject;

returning:

ProjectID StartDate EndDate WDays
1 1/1/2006 4/12/2006 73
2 2/1/2006 5/10/2006 70

or, you can return only the projects that
took greater than 70 days to complete:

SELECT
ProjectID,
StartDate,
EndDate,
WorkingDays([StartDate],[EndDate]) As WDays
FROM
tblProject
WHERE
WorkingDays([StartDate],[EndDate]) > 70;

ProjectID StartDate EndDate WDays
1 1/1/2006 4/12/2006 73

Is the question where do you put the code?

Start a new module.
In the Database Window (w/Objects
listed down the left side), click on "Modules"
then "New" in windows upper left corner.

This will open up a blank "Microsoft Visual Basic"
window.

Under "Option Compare Database", type in

Option Explicit

Copy code from web page above and paste
into this module under "Option Explicit.".
(copy only text from "Code Start" to "Code End")

Save the module (say as "modDates") by clicking
on the "floppy icon" or selecting File/Save from
top menu.

To make sure haven't done something wrong,
click on "Debug/Compile ...." in top menu.

If everything went well, you are now ready to use
the functions in a query as illustrated above...

If you want to take holidays into account,
create a table named "tblHolidays" with a field
named "HolidayDate" and enter dates applicable
to your country/region/company.

Then you can use the WorkingDays2 function in
your queries.

BTW, Helen Feddema has also looked at workdays/holidays
"from every angle" in her Archon column:

http://www.woodyswatch.com/access/archtemplate.asp?3-24

(which includes a downloadable example mdb)

I was looking at both the ways to calculate work days and my question is
where do you put these and once placed how do you call them up, is it in the
criteria or first line in a query?

Thank you in advance for any help

:

I'm sure this has probably been addressed before but here goes...i'm
trying
to use the datediff function to return the number of days between two
dates
but i only want to include weekdays. if i use DateDiff("w", [Start
Date],[End Date]) like Access help says, i get the number of weeks. This
is
a known problem with Access 97. I know how to work around it with code but
is there anyway to do this in a query?


you probably have already found this,
but just in case....

ACC2000: How to Find Number of Working Days Between Two Dates
http://support.microsoft.com/default.aspx?scid=kb;en-us;210562

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288194

 

[Gary Walter]

In fact, I would imagine this would
be a good topic for mvp site if
you have the time to write it up.

I seem to remember you posted
SQL to make a calendar table from
scratch?

Along with update queries for fields
like FiscalYear start, end, week, and
quarter; plus regional holiday fields....

I would imagine Arvin Meyer would be
happy to work with you on such a topic...

 

[Jamie Collins]

I would imagine this would
be a good topic for mvp site if
you have the time to write it up.

As I said, it's a standard trick. For example, see:

http://www.aspfaq.com/show.asp?id=2519

In this case the code is SQL Server but the concept is the same. Skip
through to the "Okay, I have a table; now what?" section and read from
there.

Jamie.

--

 

[Gary Walter]

Excellent..... don't know how I missed this site.

thanks!

"Jamie Collins" <wrote:

 

[Gary Walter]

This is not same thing as what we were discussing,
but, if nothing else, one comment by WTF-Normal Form
is pretty funny:

"Actually this {database} table is more {like} a chair
or maybe a TV-tray if we want to be really generous"

http://thedailywtf.com/forums/40314/ShowPost.aspx

hey..it's Friday...