Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculate Working days ?

Discussion in 'Information Technology' started by Martin \(Martin Lee\), Jul 28, 2006.

  1. Martin \(Martin Lee\)

    Martin \(Martin Lee\)
    Expand Collapse
    Guest

    I have two fields: one is begining date, the other is ending date.

    I want to make it calculate automatically for the working days between date1
    and date2. ( Working days: Monday to Friday, except Saturday and Sunday)

    In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
    calculate the working days. Is there any method in ACCESS?

    Thank you.


    Martin Lee
     
  2. Loading...

    Similar Threads Forum Date
    India Reading Calculated Banis Breaking News Mar 6, 2013
    Working with Christians #3 Blogs Oct 17, 2015
    Working with Christians #2 Blogs Oct 17, 2015
    Working With Muslims Blogs Oct 17, 2015
    Working with Christians Blogs Oct 17, 2015

  3. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    Press F1 (help) and search on "DateDiff"


    --
    Wayne




    "Martin (Martin Lee)" wrote:

    > I have two fields: one is begining date, the other is ending date.
    >
    > I want to make it calculate automatically for the working days between date1
    > and date2. ( Working days: Monday to Friday, except Saturday and Sunday)
    >
    > In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
    > calculate the working days. Is there any method in ACCESS?
    >
    > Thank you.
    >
    >
    > Martin Lee
    >
    >
    >
     
  4. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Function NetWorkDays(date1, date2) As Long
    Dim lngRet As Long
    Dim lngFullWeeksDays As Long
    Dim lngOddDays As Long
    Dim lngCount As Long

    Const WORK_DAYS = 5

    If Not IsDate(date1) Then Exit Function
    If Not IsDate(date2) Then Exit Function

    lngRet = DateDiff("d", date1, date2)
    lngFullWeeksDays = (lngRet \ 7) * WORK_DAYS
    lngOddDays = lngRet Mod 7

    For lngCount = 1 To lngOddDays
    Select Case DatePart("w", DateAdd("d", lngCount, date1))
    Case vbSaturday, vbSunday
    lngFullWeeksDays = lngFullWeeksDays - 1
    End Select
    Next
    lngRet = lngFullWeeksDays + lngOddDays
    NetWorkDays = lngRet
    End Function


    --

    Terry Kreft


    "Martin (Martin Lee)" <lajitong888@21cn.com> wrote in message
    news:OrHSBIwfGHA.4792@TK2MSFTNGP03.phx.gbl...
    > I have two fields: one is begining date, the other is ending date.
    >
    > I want to make it calculate automatically for the working days between

    date1
    > and date2. ( Working days: Monday to Friday, except Saturday and Sunday)
    >
    > In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
    > calculate the working days. Is there any method in ACCESS?
    >
    > Thank you.
    >
    >
    > Martin Lee
    >
    >
     
  5. Martin \(Martin Lee\)

    Martin \(Martin Lee\)
    Expand Collapse
    Guest

    Can it possible to make it as a formula in a QUERY's field, rather than use
    a FUNCTION ?

    Thanks!

    Martin Lee


    "Martin (Martin Lee)" <lajitong888@21cn.com> дÈëÏûÏ¢ÐÂÎÅ:OrHSBIwfGHA.4792@TK2MSFTNGP03.phx.gbl...
    >I have two fields: one is begining date, the other is ending date.
    >
    > I want to make it calculate automatically for the working days between
    > date1 and date2. ( Working days: Monday to Friday, except Saturday and
    > Sunday)
    >
    > In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
    > calculate the working days. Is there any method in ACCESS?
    >
    > Thank you.
    >
    >
    > Martin Lee
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I've got an expression that can be used in a query in my September, 2004
    "Access Answers" column in Pinnacle Publication's "Smart Access". You can
    download the column (and sample database) for free at
    http://www.accessmvp.com/DJSteele/SmartAccess.html

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Martin (Martin Lee)" <lajitong888@21cn.com> wrote in message
    news:eLrYXwwfGHA.1204@TK2MSFTNGP02.phx.gbl...
    > Can it possible to make it as a formula in a QUERY's field, rather than

    use
    > a FUNCTION ?
    >
    > Thanks!
    >
    > Martin Lee
    >
    >
    > "Martin (Martin Lee)" <lajitong888@21cn.com>

    дÈëÏûÏ¢ÐÂÎÅ:OrHSBIwfGHA.4792@TK2MSFTNGP03.phx.gbl...
    > >I have two fields: one is begining date, the other is ending date.
    > >
    > > I want to make it calculate automatically for the working days between
    > > date1 and date2. ( Working days: Monday to Friday, except Saturday and
    > > Sunday)
    > >
    > > In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which

    can
    > > calculate the working days. Is there any method in ACCESS?
    > >
    > > Thank you.
    > >
    > >
    > > Martin Lee
    > >
    > >

    >
    >
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Martin (Martin Lee) wrote:
    > calculate automatically for the working days between date1
    > and date2. ( Working days: Monday to Friday, except Saturday and Sunday)


    A standard trick is to use a auxillary calandar table:

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

    Jamie.

    --
     
  8. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    In a query use these

    TotalDays:DateDiff("d", [StartDate], [EndDate])

    To get rid of Sat and Sun use this
    WorkingDay:
    [EndDate]-[StartDate]-(DateDiff("ww",[StartDate],[EndDate],7)-(Weekday([StartDate])=7))-(DateDiff("ww",[StartDate],[EndDate],1)-(Weekday([StartDate])=1))

    Hope this helps

    --
    Wayne




    "Martin (Martin Lee)" wrote:

    > Can it possible to make it as a formula in a QUERY's field, rather than use
    > a FUNCTION ?
    >
    > Thanks!
    >
    > Martin Lee
    >
    >
    > "Martin (Martin Lee)" <lajitong888@21cn.com> ôÈëÃûâÃÂÎÅ:OrHSBIwfGHA.4792@TK2MSFTNGP03.phx.gbl...
    > >I have two fields: one is begining date, the other is ending date.
    > >
    > > I want to make it calculate automatically for the working days between
    > > date1 and date2. ( Working days: Monday to Friday, except Saturday and
    > > Sunday)
    > >
    > > In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
    > > calculate the working days. Is there any method in ACCESS?
    > >
    > > Thank you.
    > >
    > >
    > > Martin Lee
    > >
    > >

    >
    >
    >
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Similar Method:

    Compute the number of working days between two dates:

    WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
    ],7)+DateDiff("ww",[StartDate],[EndDate],1))


    First datediff is total number of days between dates.
    Second datediff is number of Saturdays between dates
    Third datediff is number of Sundays between dates.
    If you also want to count BOTH the first AND last day as working days
    then add 1 to result (ie. if start date is today and end date is
    tomorrow is that 2 working days or 1 working day. If two days and start
    date is always a workday then add 1 to working days, probably.)

    Ron
     

Share This Page