I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

Where A1 contains a date:

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


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

If you have a date:

A1 = some date like 4/12/2007 (April 12 2007)

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

Returns 30

Biff

"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

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

with a date in A1 and the cell with the above formatted as general it will
do what you want.

--
Regards,

Peo Sjoblom



"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

If all you have is the month name as a TEXT entry:

A1 = April or Apr

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

Returns the number of days for the month *of the current year*. Where that
might be important is in a leap year.

Biff

"T. Valko" wrote in message
...
If you have a date:

A1 = some date like 4/12/2007 (April 12 2007)

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

Returns 30

Biff

"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.

Thanks! It worked - I wanted to know how it works. I broke up the formula
and get the parts. When I put it together - DATE(YEAR(A1),MONTH(A1)+1,0) - I
get the date - 7/31/2001 and then day (7/31/2001) - 31. Why add 1 to the
month?

Thanks a ton!

"PCLIVE" wrote:

Where A1 contains a date:

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


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

This works because of the DATE command. If you used this formula:
=DATE(2007,5,1)

The result is 5/1/2007.

If you used:
=DATE(2007,5,32)
Since you are using the DATE command and Excel knows that May 32nd, 2007 is
not a valid date, it moves to the next month and displays:
6/01/2007

The same thing applies if you use:
=DATE(2007,5,0)
Again, May 0,2007 is not a valid date...so Excel moves backward to the
previous month by one day, which will be the last day of the previous month
specified (in this case, 5 or May. So by adding 1 to the current month, we
get next month...and by finding day 0 of next month, we get the last day of
THIS month.

Hope this helps,
Paul


"dipsy" wrote in message
...
Thanks! It worked - I wanted to know how it works. I broke up the
formula
and get the parts. When I put it together -
DATE(YEAR(A1),MONTH(A1)+1,0) - I
get the date - 7/31/2001 and then day (7/31/2001) - 31. Why add 1 to the
month?

Thanks a ton!

"PCLIVE" wrote:

Where A1 contains a date:

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


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.

You can also use the following. If A1 contains a date, then the number of
days in the month represented by the date is given by: =DAY(EOMONTH(A1,0))
In this example the EOMONTH function adds 0 to the month of the date in A1
and calculates the date of the last day of the month. Then the DAY function
returns the day number (1-31) of that date.
Note: You must have the Analysis Toolpack add-in installed in versions prior
to Excel 2007 to use the EOMONTH function.


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.

Returns the number of days in a month for the current year.

If all you have is the month name (as a TEXT entry) in either long or short
form:

A1 = July or Jul

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

If all you have is the month number:

A1 = 7

=DAY(DATE(YEAR(TODAY()),A1+1,0))

If you have any date for that month (with a specific year):

A1 = 7/22/2007

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

--
Biff
Microsoft Excel MVP


"Dave Thomas" wrote in message
. net...
You can also use the following. If A1 contains a date, then the number of
days in the month represented by the date is given by:
=DAY(EOMONTH(A1,0)) In this example the EOMONTH function adds 0 to the
month of the date in A1 and calculates the date of the last day of the
month. Then the DAY function returns the day number (1-31) of that date.
Note: You must have the Analysis Toolpack add-in installed in versions
prior to Excel 2007 to use the EOMONTH function.


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.