number of days in a month

R

Ryan Proudfit

I need a function or formula that will return the number of days in a month.
I saw it in one of my reference books, but I'll probably never find it again.
Please help.
 
D

Duke Carey

=DAYS(EOMONTH( target date,0))

to use EOMONTH() you must have the Analysis toolpack add-in loaded

To get the same answer without the Analysis toolpack

=DAY(Date(year(target date), month( target date)+1, 1)-1)

Duke
 
A

Arvi Laanemets

Hi


Duke Carey said:
=DAYS(EOMONTH( target date,0))

to use EOMONTH() you must have the Analysis toolpack add-in loaded

To get the same answer without the Analysis toolpack

=DAY(Date(year(target date), month( target date)+1, 1)-1)
Click to expand...

Somewhat shorter version:
=DAY(Date(year(target date), month( target date)+1, 0))


Arvi Laanemets
 
C

Chip Pearson

Ryan,

The following formula will return the number of days in the
current month.

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

It works because the 0th day of one month is the last day of the
prior month.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com









message
news:[email protected]...
 
R

Ron Rosenfeld

I need a function or formula that will return the number of days in a month.
I saw it in one of my reference books, but I'll probably never find it again.
Please help.
Click to expand...


Given any date in a month in A1, the number of days in that month is:

=32-DAY(A1-DAY(A1)+32)


--ron
 
R

Ryan Proudfit

I tried both ways and still get an error. All I'm doing is substituting a
cell reference in the target date portion of the formula. What the heck am I
doing wrong?

Ryan :\
 
A

Arvi Laanemets

Hi

Are you sure you have a date in cell, not a datestring. When you format the
cell with date as general, does it change to number? When not, then you have
to convert it, or to modify the formula so it works with datestring.


Arvi Laanemets
 
S

Sandy Mann

Somewhat shorter version:
=DAY(Date(year(target date), month( target date)+1, 0))
Click to expand...

and if you can guarantee that target date will never be later than the 28th
then

=DAY(A14+32-DAY(A14+32))

is shorter still, otherwise

=DAY(A14-DAY(A14)+32-DAY(A14-DAY(A14)+32))

Sandy
 
Top