Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 6th, 2004, 03:26 PM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Difference in Access VBA

Hi
I'm trying to calculate workdays(Excluding saturday and sunday) between 2 dates using access VBA but have not been able to do it.Can anyone help me out or point me to the piece of code for doing this.
Thanks
Amit

Reply With Quote
  #2 (permalink)  
Old January 13th, 2004, 03:12 PM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i found the solution code for counting workdays between 2 dates:

Dim dif As Integer
Dim i As Integer
Dim dCount As Integer
Dim midTemp

StartDate = "01/05/2004"
enddate = "01/15/2005" '

   dCount = 1
   dif = DateDiff("d", Startdate, enddate, vbMonday, vbFirstJan1)
   For i = 1 To dif
       midTemp = DateAdd("d", i, Startdate)
       If Weekday(midTemp, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next i
    print dCount



Reply With Quote
  #3 (permalink)  
Old January 13th, 2004, 03:43 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

That code looks interesting but there is a weekday counter option on the DateDiff function. DateDiff("w", Start Date, End Date).

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #4 (permalink)  
Old January 14th, 2004, 10:08 AM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Interestingly DateDiff("w", Start Date, End Date) returns the number of weeks between the two dates as opposed to what the normal behavior would be to count the workdays skipping over the weekends.
-amit


Reply With Quote
  #5 (permalink)  
Old January 14th, 2004, 10:55 AM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by amit_jain76
 Interestingly DateDiff("w", Start Date, End Date) returns the number of weeks between the two dates
 Correction: "ww" returns the number of weeks. "w" returns the number of weekdays.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
get date difference benz_jie2005 Access 1 January 16th, 2008 08:33 AM
Date Difference Help saf01 Crystal Reports 0 September 29th, 2005 05:35 AM
Difference between Date and Now() marcin2k Access VBA 3 September 8th, 2005 01:05 PM
Date conversion in Access VBA goels Access VBA 2 October 13th, 2004 12:26 PM
Date in Access VBA (SQL) Kenny Alligood Access VBA 2 September 14th, 2003 08:35 AM



All times are GMT -4. The time now is 09:51 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.