Count Weeks per mth

J

June

Hi Everyone,

Beginner seeking for helps!!

I wish to count how many weeks in certain month. All I knw is able to count
weeks per year...is that possible to count weeks per month. For example, I
enter January 2004 then it able to return me with 4 weeks. If Feb 2004 then
it able to return me 4 weeks and so on, instead of returning me 12 weeks of
the year is for which certain date. In short, I jus would like to count how
many weeks in certain month instead of years. Thanks in advance.
 
B

Brendan Reynolds

Most months will not contain a whole number of weeks - they will contain
4-and-a-bit or 5-and-a-bit weeks. You haven't indicated whether you want to
return fractional numbers (e.g. 4.5 weeks), or to round up or down. So I've
taken the easy way out in this example, and simply counted Sundays ...

Public Function CountOfSundays(ByVal intMonth As Integer, ByVal intYear As
Integer) As Integer

Dim dtmLoop As Date
Dim intSundays As Integer

For dtmLoop = DateSerial(intYear, intMonth, 1) To DateSerial(intYear,
intMonth + 1, 0)
If Weekday(dtmLoop, vbSunday) = vbSunday Then
intSundays = intSundays + 1
End If
Next dtmLoop

CountOfSundays = intSundays

End Function

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

June

Thanks for your help. It solves part of my problems.

There is a que here, when I search for March, it shud return me 5 weeks,
instead it returns me 4 weeks now. I wonder why. I want to round up, whenever
is 4.5 weeks or 4.1 weeks, I wanna round up to 5 weeks. Can pls help, and
besides this, is that possible to list out all dates in week 1, dates in week
2 and so on. Pls help, thanks!
 
B

Brendan Reynolds

Hey, one question at a time, please! :)

There are 31 days in March. That's four weeks and three days, or
4.42857142857143 weeks. You really want to count that as five weeks?

If you are always going to round up, there will always be five weeks in
every month, except when it is February and it is not a leap year ...

Public Function WeeksRoundedUp(ByVal intMonth As Integer, ByVal intYear As
Integer) As Integer

Dim dtmLast As Date

'Get last day of month
dtmLast = DateSerial(intYear, intMonth + 1, 0)

If Day(dtmLast) = 28 Then
WeeksRoundedUp = 4
Else
WeeksRoundedUp = 5
End If

End Function

.... but are you sure that's what you want?

You might want to check out the Date/Time section at the Access Web page:
http://www.mvps.org/access/datetime/index.html

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Top