Number of days in a month

=DAY(DATE(YEAR(A2),MONTH(A2)+1,))

Mark,

That works, but I don't BEGIN to understand why. Would you walk me through it so I know what's going on?

Thanks!

The code above works like this:

In some cell, you have a date. Let's say it's A1. This date can be broken down into it's components (day, month, year) by using the built-in excel functions:

DAY(), YEAR(), MONTH().

Also, excel makes available the function called "DATE()" to build a date, if you have the three components. If you placed:

=DATE(2003, 1, 2)

in a cell on your worksheet, that cell would then become the date "January 2nd, 2003", depending on how you formatted it.

So, you can construct dates out of three components, using the DATE function.

This is all pulled together in the example given above, when you say:

=DATE(YEAR(A1), MONTH(A1) + 1, 0)

You're effectively saying:

* The year is the same as the YEAR() in cell A1.

* The month is one more than the month in cell A1.

* The day is zero.

Think about that last bit. What's January 0th, 2002?

To excel, the zero-th day of a month, is the last day of the PREVIOUS month. So January 0, 2002, is actually December 31, 2001.

After all that, you surround the entire formula with the DAY() function, in order to pull out just the 2 digit day. In the case of January 0, 2002, you'll pull out the 31 from December.



I've struggled with this same issue for some time. Calculating the end of the month is something we do quite often here with our spreadsheets, so here's a function that you can place in your VB project, so that you can just type:

=EndOfMonth(A1)

In a cell, and if A1 is a date, you'll get the last day of that date.

Place this code in a new module:<pre>
Function EndOfMonth(mDate As Date) As Variant

EndOfMonth = Day(DateSerial(Year(mDate), Month(mDate) + 1, 0))

End Function</pre>

_________________<form action="http://www.google.com/search" name=f> <input style="border:1px solid;" maxLength=256 size=15 name=q value=""><input type=submit value="Search" name=btnG></form>
This message was edited by OdinsDream on 2003-01-06 14:09

The DATE worksheet function returns the datevalue for a given set of arguments that include year, month, day. If day is 0 or omitted DATE returns the datevalue for the last day of the preceeding month. So, I added 1 to the month and omitted the 3rd argument to get the datevalue for 1/31/2002. The DAY function converts a datevalue to an integer between 1 and 31 representing the day portion of the date.
This message was edited by Mark W. on 2003-01-06 14:11

There's no reason to write VBA code for a EOM function since this function already exists in the Analysis ToolPak...

=DAY(EOMONTH(A2,0))

I recommended DAY(DATE(...)) only to avoid the use of thec Analysis ToolPak!
This message was edited by Mark W. on 2003-01-06 14:15

Well, everybody has their own preference. Since our workbooks are distributed to many different machines, we can't control whether the Analysis ToolPak is installed or not.

We also calculate the End-of-Month within many VB macros in the workbooks, so it's just easier to call the function.

to each his own.

...which is why I recommended...

=DAY(DATE(YEAR(A2),MONTH(A2)+1,))

If one would like to hide the complexity of this formulation... It's easy enough to create a defined name, EndOfMonth, that refers to =DAY(DATE(YEAR(A2),MONTH(A2)+1,)).
This message was edited by Mark W. on 2003-01-06 14:42

Thanks for the explanation. My thought process was working on a max function and getting nowhere!

The date of zero taking you back a month was the critical missing information, although the truth is I'd have been hard pressed to USE that information.

Thanks for the formula AND the lesson!

Mark...Superb work....