Prev: extracting day from date
Next: getting data from date range
From: CJ on 18 Mar 2010 10:21
I am trying to work out the number of working days between two dates.
I am using the DateDiff function, but this appears to count weekends.
Giving me fault results.
Thurs 12th to Tue 17th, should return 3, but returns 5.

Is there an interval for this, or should I use a different function.

I know in Excel its NETWORKDAYS.

Thanks
From: John W. Vinson on 18 Mar 2010 12:01
On Thu, 18 Mar 2010 07:21:01 -0700, CJ <CJ(a)discussions.microsoft.com> wrote:

>I am trying to work out the number of working days between two dates.
>I am using the DateDiff function, but this appears to count weekends.
>Giving me fault results.
>Thurs 12th to Tue 17th, should return 3, but returns 5.
>
>Is there an interval for this, or should I use a different function.
>
>I know in Excel its NETWORKDAYS.
>
>Thanks

There's nothing builtin in Access to do this (a bit odd, but that's how MS
programmed it!). You will need to add some custom code; there's good sample
code at
http://www.mvps.org/access/datetime/date0012.htm

As noted on the webpage, you'll probably want to create a table of the olidays
that your organization observes.
--

John W. Vinson [MVP]
From: kc-mass on 18 Mar 2010 12:08
Look Here: http://www.mvps.org/access/datetime/date0006.htm

Regards

Kevin


"CJ" <CJ(a)discussions.microsoft.com> wrote in message
news:0511788C-831D-47DA-84DB-A0C21AEBB250(a)microsoft.com...
>I am trying to work out the number of working days between two dates.
> I am using the DateDiff function, but this appears to count weekends.
> Giving me fault results.
> Thurs 12th to Tue 17th, should return 3, but returns 5.
>
> Is there an interval for this, or should I use a different function.
>
> I know in Excel its NETWORKDAYS.
>
> Thanks


From: vanderghast on 18 Mar 2010 14:36
If there is a large interval of dates between the two dates, and if you are
only interested in removing Sunday and Saturdays, you can do the following:

DateDiff( "w" , startingDate, endingDate) ' returning the number of
days, including Sunday and Saturday
- DateDiff("ww", startingDate-1, endingDate, vbSunday ) ' the number
of Sundays
- DateDiff("ww", startingDate-1, endingDate, vbSaturday) ' the number
of Saturdays.


You would have to manually remove any non working day falling / reported on
a weekday.

I assumed your starting date is later than the 31st December 1899. If not,
you will have to use DateAdd('w", -1, startingDate) rather than
startingDate-1.


Vanderghast, Access MVP



"CJ" <CJ(a)discussions.microsoft.com> wrote in message
news:0511788C-831D-47DA-84DB-A0C21AEBB250(a)microsoft.com...
>I am trying to work out the number of working days between two dates.
> I am using the DateDiff function, but this appears to count weekends.
> Giving me fault results.
> Thurs 12th to Tue 17th, should return 3, but returns 5.
>
> Is there an interval for this, or should I use a different function.
>
> I know in Excel its NETWORKDAYS.
>
> Thanks

 | 
Pages: 1
Prev: extracting day from date
Next: getting data from date range