Results 1 to 6 of 6

Thread: Working Days

  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: Working Days

    Hi,

    In excel its a simple formula of =NETWORKDAYS(A1,B1) and there is the answer.

    How in my query can I work out the working days difference between job visit and job completion. Also in excel its easy to group a set of results together, how can I say if the days difference is between 0&7 then 1 Week, between 8&14 then 2 Week, between 15&21 then 3 Week and if anymore then 4 Weeks or More?

    Thanks,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are interested in working days, would you count december 25 as a working day or not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    6
    No I would exclude bank holidays

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    then you need a calendar table

    one row per date, whether normal workday, weekend day, bank holiday, whatever

    a calendar table takes only a few minutes to set up, but it will make your queries positively fly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by http://www.mvps.org/access/datetime/date0006.htm
    Note that this function does not account for holidays
    nothing beats a calendar table, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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