How many useful days has a month?

[Joao]

I want to detect how many duty working days has a specific month (Not
counting holidays or whatever). Ex: This year, August has got 21 days out of
31.

Any simple function out there?

 

[bcap]

There will not be a generic function for this because it depends on where
you are! Different countries (and even different states within countries)
have different holidays! Plus, some holidays (like Easter) have a nasty
habit of not staying in the same place every year!

What I usually do is to create a calendar table going many years into the
future, with the following fields:

calendar_date
working_day

Where working_day is a Boolean (Yes/No) field.

It's easy enough to populate this automatically for many years into the
future, setting working_day to Yes for weekdays and No for weekends. IIRC,
I first created all the dates in Excel (a simple formula and then Fill Down)
and imported them. Now I just copy the table from one database to another.

A simple form allows users to manually set the holidays for as far ahead as
they want to go.

Once you've got a table like this, it's amazing how many uses you can find
for it! In your case, a simple query gives you your answer:

SELECT Count(calendar_date) FROM calendar WHERE working_day = True AND
calendar_date BETWEEN #08/01/2008# AND #08/31/2008#

Or, using DCount:

DCount("calendar_date","calendar", "working_day = True AND calendar_date
BETWEEN #08/01/2008# AND #08/31/2008#")

 

[Chris]

Check out Doug Steele's article in Smart Access.
http://www.accessmvp.com/djsteele/SmartAccess.html

I believe June 2005 will help you. It works for me.

HTH,
Chris

 

[Chris O''''Neill]

But he said he wanted a *simple* function! <g>

Seriously, though, that's a *fabulous* article! Mr. Steele is a genius! I
haven't a need for that right now, but I bookmarked it anyway because I know
someday it'll come in handy.

Thanks for pointing that out!

Regards, Chris (the other one!) <g>

 

[Joao]

I told I don't give a damn about the holidays, only normal duty working days
i.e. except Saturday and Sunday.

 

[Joao]

Thanks Chris, but i have filtered internet and can't see the webpage.

 

[John Spencer]

I can't say for sure that these will work. I pulled it from an archive of
tips and tricks and have not tested either of the two functions.


'============================================================
' function by Klatuu
' Weekdays (no adjustment for holidays)
'============================================================
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
End Function


'===========================================================
' Doug Steele
' Weekdays (no adjustment for holidays)
'===========================================================
SQL Statement:
'Number of weekdays between two dates, by Doug Steele MVP

SELECT DateDiff("d", dte1, dte2) -
DateDiff("ww", dte1, dte2, 1) * 2 -
IIf(Weekday(dte2, 1) = 7,
IIf(Weekday(dte1, 1) = 7, 0, 1),
IIf(Weekday(dte1, 1) = 7, -1, 0)) As WeekdaysBetween2Dates
FROM tblMyTable

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bcap]

If you don't like the advice just ignore it. If you want to give attitude
to people who are trying to help you, I suggest you go *pay* someone.
..

 

[bcap]

I'll save you the trouble, it's all about holidays, so you might as well
just insult him and move on.

 

[Joao]

Hey bcap, I apologize for the answer, i am not keen to english language so I
didn't notice the conotation it could give. It wasn't me being rude, I was
trying to tell u I don't need to know about the holidays except for Sat and
Sunday. Sorry for the misunderstood.

 

[Joao]

Thank u John, really appreciated!

I can't say for sure that these will work. I pulled it from an archive of
tips and tricks and have not tested either of the two functions.


'============================================================
' function by Klatuu
' Weekdays (no adjustment for holidays)
'============================================================
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
End Function


'===========================================================
' Doug Steele
' Weekdays (no adjustment for holidays)
'===========================================================
SQL Statement:
'Number of weekdays between two dates, by Doug Steele MVP

SELECT DateDiff("d", dte1, dte2) -
DateDiff("ww", dte1, dte2, 1) * 2 -
IIf(Weekday(dte2, 1) = 7,
IIf(Weekday(dte1, 1) = 7, 0, 1),
IIf(Weekday(dte1, 1) = 7, -1, 0)) As WeekdaysBetween2Dates
FROM tblMyTable

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bcap]

No problem, apology accepted. I see the other guys have given you a couple
of options for your simple (holiday-free!) calculation.

Good luck!

 

[Michel Walsh]

DateDiff("ww", date1, date2, vbSunday)


return the number of week boundaries crossed within the interval [date1,
date2] if we consider the week boundaries start a Sunday (at 00:00:00). In
other words, if date1 is NOT a Sunday, it returns the number of Sundays in
the interval [date1, date2]. Add 1 to the count if date1 is a Sunday.

Also,


DateDiff("ww", date1, date2, vbSaturday)


would also return the number of Saturday (if date1 is not a Saturday; add 1
in that case) between date1 and date2.


So, the total number of days less the sum of these two last expressions give
the number of day not a Sunday, neither a Saturday.


DateDiff("d", date1, date2) + 1
- ( DateDiff("ww", date1, date2, vbSunday) - (Datepart("w", date1) =
vbSunday))
- ( DateDiff("ww", date1, date2, vbSaturday) - (Datepart("w", date2) =
vbSaturday))



should do (the +1 is required since we want from 1 to 3 to return 3, and 3
= ( 3 -1) + 1 )



Vanderghast, Access MVP