What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

As far as I know there is not a direct formula for this, however, by
combining several formulas it is possible to solve.

Let's say the "randomly selected date" is in cell A1, then put this formula
in A2 to display the number of days in that month:
=EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1

This is what happens:
EOMONTH(A1;0) returns the serial number of the last day in the moth of the
date in A1
YEAR(A1) returns the year of the date in A1
MONTH(A1) returns tha month of the date in A1
DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month
as A1, day 1 of that month
if you subtract the last day of the month with the first day of the month
and add 1 then you get the total number of days in that month

NOTE: Excel will most probably automatically change the Number format of
cell A2 to date-time in this case, which does not give you any useful output.
Select A2, choose format cells and change number to "General" (or to "Number"
with "decimal places" = 0)

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
seems to me a little bit simpler and doesn't require Analysis Toolpak
(EOMONTH does). You have to format the result cell as General in this case,
too.

Regards,
Stefi

€žValdus€� ezt Ã*rta:

As far as I know there is not a direct formula for this, however, by
combining several formulas it is possible to solve.

Let's say the "randomly selected date" is in cell A1, then put this formula
in A2 to display the number of days in that month:
=EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1

This is what happens:
EOMONTH(A1;0) returns the serial number of the last day in the moth of the
date in A1
YEAR(A1) returns the year of the date in A1
MONTH(A1) returns tha month of the date in A1
DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month
as A1, day 1 of that month
if you subtract the last day of the month with the first day of the month
and add 1 then you get the total number of days in that month

NOTE: Excel will most probably automatically change the Number format of
cell A2 to date-time in this case, which does not give you any useful output.
Select A2, choose format cells and change number to "General" (or to "Number"
with "decimal places" = 0)

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

Hi Narnimar,

Here's another way:
=DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(A1),MONTH(A1),0)

--
Cheers
macropod
[MVP - Microsoft Word]


"Narnimar" wrote in message ...
What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

I copied it but Formula throws an error to me. Thanks to you to verify and
reply.

"Valdus" wrote:

As far as I know there is not a direct formula for this, however, by
combining several formulas it is possible to solve.

Let's say the "randomly selected date" is in cell A1, then put this formula
in A2 to display the number of days in that month:
=EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1

This is what happens:
EOMONTH(A1;0) returns the serial number of the last day in the moth of the
date in A1
YEAR(A1) returns the year of the date in A1
MONTH(A1) returns tha month of the date in A1
DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month
as A1, day 1 of that month
if you subtract the last day of the month with the first day of the month
and add 1 then you get the total number of days in that month

NOTE: Excel will most probably automatically change the Number format of
cell A2 to date-time in this case, which does not give you any useful output.
Select A2, choose format cells and change number to "General" (or to "Number"
with "decimal places" = 0)

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

Maybe a bit shorter

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

Format as general

Mike

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

As Stefi mentioned EOMONTH needs Analysis Toolpak to be installed.
This is a very useful toolpak to have installed, but I think you have
received a lot of help from the other users where you don't need it for this
issue.

"Narnimar" wrote:


I copied it but Formula throws an error to me. Thanks to you to verify and
reply.

"Valdus" wrote:

As far as I know there is not a direct formula for this, however, by
combining several formulas it is possible to solve.

Let's say the "randomly selected date" is in cell A1, then put this formula
in A2 to display the number of days in that month:
=EOMONTH(A1;0)-DATE(YEAR(A1);MONTH(A1);1)+1

This is what happens:
EOMONTH(A1;0) returns the serial number of the last day in the moth of the
date in A1
YEAR(A1) returns the year of the date in A1
MONTH(A1) returns tha month of the date in A1
DATE(YEAR(A1);MONTH(A1);1) returns the date with same year as A1, same month
as A1, day 1 of that month
if you subtract the last day of the month with the first day of the month
and add 1 then you get the total number of days in that month

NOTE: Excel will most probably automatically change the Number format of
cell A2 to date-time in this case, which does not give you any useful output.
Select A2, choose format cells and change number to "General" (or to "Number"
with "decimal places" = 0)

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

Try this

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



On Sep 19, 2:16*am, Narnimar
wrote:
What is the formula to return the # of days in that month *for a randomly
selected date? Thanks for the help.

This is very cute. Thanks a lot.

"Mike H" wrote:

Maybe a bit shorter

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

Format as general

Mike

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.

Well I've rarely heard of a formula described as 'Cute' but there we are!!.
Thanks for the feedback.

Mike

"Narnimar" wrote:

This is very cute. Thanks a lot.

"Mike H" wrote:

Maybe a bit shorter

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

Format as general

Mike

"Narnimar" wrote:

What is the formula to return the # of days in that month for a randomly
selected date? Thanks for the help.