# of days in that month for a selected date

N

Narnimar

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

Valdus

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

Stefi

=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:
 
M

macropod

Hi Narnimar,

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

Valdus

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

Mike H

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

Mike
 
Top