number of days in a month

[Guest]

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.

 

[Guest]

=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

 

[Arvi Laanemets]

Hi

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

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


Arvi Laanemets

 

[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]...

 

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

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

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


--ron

 

[Guest]

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 :\

 

[Guest]

That's a new one on me., Arvi. Live and learn (and learn, and learn)

 

[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

 

[Sandy Mann]

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

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