Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    15

    Question Unanswered: Calculating days between two dates

    Hi all,

    I need to get the difference of two dates but it should deduct the Saturday and Sunday. Say for example (March 8 - March 2). the datediff function returns 6. But March 6 and 7 are weekends, so it should not consider. My output should reflect 4 days only. Is there any other date fuction in VBA which can do this?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Have a look at this entry in the Code Bank http://www.dbforums.com/6256524-post15.html

  3. #3
    Join Date
    Sep 2009
    Posts
    15
    Thanks, I'll try this codes you've given me.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's more date type calculation coding in the code bank here: http://www.dbforums.com/6296489-post45.html

    Note this (you can use these as part of your query expression):

    To get the 1st Sunday of MyDateField:
    Format((MyTable.MyDateField)-Weekday([MyTable.MyDateField])+1,"mm/dd/yyyy")

    To get the 1st Saturday of MyDateField:
    Format((MyTable.MyDateField)-Weekday([MyTable.MyDateField])+7,"mm/dd/yyyy")

    To get the 1st Monday of MyDateField:
    Format((MyTable.MyDateField)-Weekday([MyTable.MyDateField])+2,"mm/dd/yyyy")

    For Friday:
    Format((MyTable.MyDateField)-Weekday([MyTable.MyDateField])+6,"mm/dd/yyyy")

    To get the 1st Monday from today's date:
    Format((Date())-Weekday(Date())+2,"mm/dd/yyyy")
    Last edited by pkstormy; 07-17-10 at 12:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •