Days in a Month

[RobV]

The code snippet below determines the number of days in a month. I thought
others might find it useful. An explanation of the code follows the snippet.

Dim TargetMonth as Date
Dim NumOfDays as Long

TargetDate = CDate("Feb 2008") 'Date format is flexible
NumOfDays = Day(Dateserial(Year(TargetDate),Month(TargetDate)+1,0))

Explanation
First, the syntax of Dateserial is Dateserial(Year,Month,Day). Second,
starting with the end of the snippet and working backwards: the day is 0.
What is Day 0? It is the day before Day 1 of a month, i.e., the last day of
the previous month. The month is picked out of the TargetDate, e.g., Feb.
Because of what was just said about the day, we need to advance the month by
one from what is stated in the target date. In the example, we add one month
to Feb to get Mar. Day 0 then becomes the last day of the preceding month,
i.e., Feb: either 28 or 29, depending on leap year. The Year resolves that
ambiguity. It identified the Year in the TargetDate. In the example, that
is 2008. So, Dateserial returns the date: "2008 Feb 29". The Day operator
then returns the numeric equivalent of the day in the date, i.e., 29. That's
the number of days in the month.

For me, the snippet has replaced the loops and Case statements that I
previously used to determine the number of days in a month. I hope it does
the same for you.

 

[John]

The code snippet below determines the number of days in a month. I thought
others might find it useful. An explanation of the code follows the snippet.

Dim TargetMonth as Date
Dim NumOfDays as Long

TargetDate = CDate("Feb 2008") 'Date format is flexible
NumOfDays = Day(Dateserial(Year(TargetDate),Month(TargetDate)+1,0))

Explanation
First, the syntax of Dateserial is Dateserial(Year,Month,Day). Second,
starting with the end of the snippet and working backwards: the day is 0.
What is Day 0? It is the day before Day 1 of a month, i.e., the last day of
the previous month. The month is picked out of the TargetDate, e.g., Feb.
Because of what was just said about the day, we need to advance the month by
one from what is stated in the target date. In the example, we add one month
to Feb to get Mar. Day 0 then becomes the last day of the preceding month,
i.e., Feb: either 28 or 29, depending on leap year. The Year resolves that
ambiguity. It identified the Year in the TargetDate. In the example, that
is 2008. So, Dateserial returns the date: "2008 Feb 29". The Day operator
then returns the numeric equivalent of the day in the date, i.e., 29. That's
the number of days in the month.

For me, the snippet has replaced the loops and Case statements that I
previously used to determine the number of days in a month. I hope it does
the same for you.

RobV,
I'm sure you spent many frustrating hours coming up with your formula
and it is rewarding when you finally get something that works. I've done
similar things myself. It is also a bummer when I later find out that
there is already a function or property that does that same thing. Take
a look at the following.

activeproject.Calendar.Years(2008).Months(pjfebruary).Days.Count

John
Project MVP

 

[RobV]

There's more than one way to skin a cat. (Is that politically incorrec?)

PS How do you set the reserved word "pjfebruary"? Can it be derived
directly from the date or is a Select statement required?

 

[John]

There's more than one way to skin a cat. (Is that politically incorrec?)

PS How do you set the reserved word "pjfebruary"? Can it be derived
directly from the date or is a Select statement required?

RobV,
Actually I'm a dog person so I have no problem with the saying about
skinning a cat - as long as it is only a saying. And yes there are often
many ways to achieve a desired goal. I am not saying that your derived
method is any less elegant than the one I suggested. I don't always use
built-in methods myself, sometimes I find that my derived method is
superior for various reasons. Plus, being willing and able to derive
your own formula gives you more tools in your arsenal and the
satisfaction that you solved a problem.

With regard to your PS, I guess I don't quite understand what you are
asking. "pjfebruary" is a constant, not a reserved word.

John
Project MVP