Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 2nd, 2004, 11:52 AM
Registered User
 
Join Date: Nov 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default calculate weekdays

Hi,

I need to calculate the difference betwen two dates. without the weekend (Saturday and Sunday) to display the actual "Work" days.

Anybody knows how to do this ?

Thanx.


 
Old September 9th, 2004, 03:00 PM
Authorized User
 
Join Date: Sep 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to rhysduk Send a message via Yahoo to rhysduk
Default

You can use the DAYS360 function.. but this is not exactly what you want..

Rhys

Forget the above, http://office.microsoft.com/en-gb/as...1033.aspx]This is what you want :)
 
Old September 10th, 2004, 09:53 PM
Registered User
 
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

create a table (i called it pivot_table), and in that, put a single column, populating it with a row for every day that might exist between your day range (I only populated 1-366)
So: pivot_table
field: Counter, Number
values: 1 through 366

select count(*)
from pivot_table
where counter <= [end_date] - [start_date] + 1
and weekday([start_date]+counter-1) not in (1,7) /* Sun,Sat */
and [start_date]+counter-1 /* a day */
not in
(select Holiday_Date
   from holidays )

This concept comes from asktom.oracle.com, converted to Access.


 
Old September 15th, 2004, 05:54 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jos,
This is what I have:
1) start field as date
2) end field as date

On the start and end field,
you can calculate what day of the week it is by:
3) num1_start = Format([start], "w")
4) num2_end = Format([end], "w")

Then calculate to exclude weekends as follows:
5) day_num1 = IIf(num1_start]]>6,0,[num1_start]-1)
6) day_num2 = IIf(num2_end]]>6,0,[num2_end]-1)

con't...
7) days_worked_on_start_week =
IIf([day_num1]=0,0,DatePart("w",6-[day_num1]))

8) days_worked_on_end_week =
IIf([day_num2]=0,5,DatePart("w",[day_num2]))

9) days_worked_without_startweek_and_endweek =
((DateDiff("ww",[start],[end])-1)*5)

10) total_num_of_days_worked =
    [7]++[9]

It should work.
john

simplicity is beauty...





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate ivangr Visual Basic 2005 Basics 0 April 9th, 2006 04:39 AM
Using weekdays in a formula narooma.12 Crystal Reports 0 May 6th, 2005 08:37 PM
calculate the values up crmpicco Classic ASP Basics 3 February 1st, 2005 10:18 AM
Help me How to calculate minhtri VB How-To 4 June 8th, 2004 04:06 PM
Adding weekdays KevinO Beginning VB 6 1 February 2nd, 2004 05:00 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.