How many useful days has a month?

  • Thread starter Thread starter Joao
  • Start date Start date
J

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?
 
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#")
 
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>
 
I told I don't give a damn about the holidays, only normal duty working days
i.e. except Saturday and Sunday.
 
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
 
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.
..
 
I'll save you the trouble, it's all about holidays, so you might as well
just insult him and move on.
 
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.
 
Thank u John, really appreciated!

John Spencer said:
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
Click to expand...
 
No problem, apology accepted. I see the other guys have given you a couple
of options for your simple (holiday-free!) calculation.

Good luck!
 
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
 
Back
Top