# of days in month
 

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.

# of days in month
 

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.

# of days in month
 

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.

# of days in month
 

=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.

# of days in month
 

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.

# of days in month
 

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.

# of days in month
 

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.

# of days in month
 

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.

# of days in month
 

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.