Hello Master,


Is it Possible to put Networkdays with Holidays formula in Ms Access


Please Help me it's Very urgent for me.


Advance Thnx,

Regards,
Vikas

Vikas Chauhan wrote:
and get your answer in minutes rather than posting this _very_ frequently
asked question here and waiting possibly for hours for someone to reply
(yes, I woke up early this morning so I'm looking at this group a few hours
earlier than I normally would so you did not have to wait for hours, but you
still could have already been working on your solution instead of waiting
for me to reply)? Here:

http://www.google.com/search?aq=f&so...days+in+Access

Now if you have any specific questions about which solution to choose or how
to implement a suggested solution, please do not hesitate to post a
follow-up question here

Hey Bob,

I have already find out in Google and other search engines,, after lot
of self try and searching, I put the question in our access group, but
still i have no successes,

I request you to please help me it very urgent for me.

also if you have Access Book and Access VBA book so please forward me
if is it possible

Thnx Bob for reply.

Regards,
Vikas Chauhan
vikask008 (AT) gmail (DOT) com,vikas... usa (DOT) com
9911868518


On Jun 4, 3:06*pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:

No, it is not possible for me to "forward" you any books that I purchased.
If you want a book, go to the library and borrow one, or go to a bookstore
and buy one.

There are two approaches to this problem. The first involves using VBA as
discussed in this article:
http://access.mvps.org/access/datetime/date0006.htm
Specifically look at the second method since it takes holidays into account.
Read it carefully! Especially the part where it explaiins that you need to
create a table to store the dates you wish to treat as holidays!

The second approach has the advantages of being more flexible and requiring
no VBA, but does require more upfront maintenance. It involves creating a
calendar table that stores a record for each day in a year. It will have
fields for CalendarDate (date/Time), isWeekend, isHoliday and isWorkingDay
(all Yes/No fields). You might be wondering why those three fields are
needed. Well, not all non-working days are holidays (and vice versa - not
all holidays are non-working days), and not all weekend days have to be
non-working days, thus the extra flexibility I mentioned. Also, if this
database needs to be used in multiple countries/locations that have
different holidays and work schedules, add another column for
country/location, so location-specific schedules can be added. Also, if you
need to use a fiscal calendar, you can add fields to describe the fiscal
year, period and week. See? Flexibility. The price is some extra maintenance
once per year.

In order to completely eliminate the need for VBA from the process of
populating tCalendar, add another table called tNumbers, to contain numbers
from 0 to 1000 to use in calculations in a field called ThisNumber with
datatype Number (subtype Short). This is well worth the small amount of
trouble needed to create it - there are many uses for a table of numbers.

You can simply create the table with a single short integer field called
ThisNumber and manually populate it with 1001 records (it only needs to be
done once) or use a VBA procedure in a standard module to populate it:

sub PopulateNumbers
dim i as integer
for i = 0 to 1000
db.execute "insert into tNumbers values (" & i & ")",dbFailOnError
next
end sub
Again, this only needs to be run once.

Now, to populate your calendar table, copy this sql into the SQL View of a
query window and save it as qInitializeYear:

PARAMETERS [Enter Year] SMALLINT;
insert tCalendar (CalendarDate)
select DateAdd("d",ThisNumber,DateSerial([Enter Year],1,1))
FROM (SELECT ThisNumber FROM tNumbers
WHERE ThisNumber < 366) As q
WHERE
Year(DateAdd("d",ThisNumber,DateSerial([Enter Year],1,1))) = [Enter Year]

Run it, enter the year you want to initialize when prompted, and open the
tCalendar table to see the result.

You can use a query to set the weekend flag. Again, open a new query window
and switch to SQL View to enter this sql and save the query as
qUpdateWeekends:

PARAMETERS [Enter Year] SMALLINT;
UPDATE tCalendar
SET IsWeekend =
Iif(DatePart("w",CalendarDate) between 1 and 5, 0, -1)
WHERE Year(CalendarDate)=[Enter Year]

Run it, enter the year you want to update when prompted, and open the
tCalendar table to see the result.

You will need to manually update the IsWorkingDay (optional, if you do not
need the flexibility of scheduling workdays on weekends and holidays) and
IsHoliday fields since there is no algorithm to calculate those.

Once that is done, this query will count the number of working days between
two dates:
SELECT Count(*) FROM tCalendar
WHERE CalendarDate BETWEEN [Enter Start Date in yyyy-mm-dd format]
and [Enter End Date in yyyy-mm-dd format]
AND IsWorking = True

or, if you have not bothered with the IsWorking field:
SELECT Count(*) FROM tCalendar
WHERE CalendarDate BETWEEN [Enter Start Date in yyyy-mm-dd format]
and [Enter End Date in yyyy-mm-dd format]
AND IsWeekend =False and IsHoliday = False


Vikas Chauhan wrote:

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

excluding holidays between 2 dates.

WorkDayCount = DateDiff("d", dtStartDate, dtEndDate) + 1 - (DateDiff("ww",
dtStartDate, dtEndDate) * 2) - IIf(WeekDay(dtStartDate) = vbSunday, 1, 0) -
IIf(WeekDay(dtEndDate) = vbSaturday, 1, 0)

Subtract out holidays from this count, using a function to count holidays in
the range of dates.

ron paii wrote:
That's certainly a viable alternative to the method shown in first function
in the above article. I would change it to "WeekDayCount=" since that is a
better description of what it counts..
That's the hard part. Why did you leave out how to do it? ;-)
Fortunately, the article cited above describes how to do this part as well.
It's just not as flexible as the second approach I described. If the OP does
not need that flexibility, then I certainly recommend he use a combination
of your expression and tblHolidays as described in the above article.

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

saves tblHolidays to a static array on the 1st call. This greatly speeds up
the holiday search on all other calls. I left it out because it had added
complexity specific to my application.

Hi Gurus,

Thnx for reply !!!

But my problem still not shotout..

can u give me sample if is it possible.

regards,
vikas


On Jun 8, 3:31*am, "ron paii" <n... (AT) nospam (DOT) com> wrote:

"Vikas Chauhan" <vikask008 (AT) gmail (DOT) com> wrote

Here is the workday count calculation with holidays. Change tblHolidays to
your holiday table name and [dtDate] to the name of your date field.

WorkDayCount = DateDiff("d", dtStartDate, dtEndDate) + 1 - (DateDiff("ww",
dtStartDate, dtEndDate) * 2) - IIf(WeekDay(dtStartDate) = vbSunday, 1, 0) -
IIf(WeekDay(dtEndDate) = vbSaturday, 1, 0) -
dcount("[dtDate]","tblHolidays","[dtDate] between #" & dtStartDate & "# and
#" & dtEndDate & "#")

how to use??

On Jun 8, 6:04*pm, "ron paii" <n... (AT) nospam (DOT) com> wrote: