# of days in month

[Guest]

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.

 

[PCLIVE]

Where A1 contains a date:

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

 

[T. Valko]

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

 

[Peo Sjoblom]

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

 

[T. Valko]

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

 

[Guest]

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]

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

 

[Dave Thomas]

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.

 

[T. Valko]

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))