Month Formula
 

I am working on a spreadsheet that has monthly data for a five-year period. I
need to convert all the data for each month into daily data. I am simply
dividing the total for each month by the number of days in each respective
month..Instead of copy and paste, or drag and drop, is there a formula that
allows a user to click on a cell containing the name of a month and have
excel recognize the number of days in that month? For example, if A1 has
"September" and B1 has 1,000,000, can I insert a formula in C1 that allows me
to do =B1/A1 instead of B1/30?

Sorry for the confusion and thanks for the help.

Month Formula
 

With your date in A1:

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

HTH,
Paul

--

"scott" wrote in message
...
I am working on a spreadsheet that has monthly data for a five-year period.
I
need to convert all the data for each month into daily data. I am simply
dividing the total for each month by the number of days in each respective
month..Instead of copy and paste, or drag and drop, is there a formula
that
allows a user to click on a cell containing the name of a month and have
excel recognize the number of days in that month? For example, if A1 has
"September" and B1 has 1,000,000, can I insert a formula in C1 that allows
me
to do =B1/A1 instead of B1/30?

Sorry for the confusion and thanks for the help.

Month Formula
 

Thanks for the formula, but it does not recognize that February has 28/29
days...it returns "30" for all February months in the five-year period. What
change do I need to make in the formula you provided me to fix this?

"PCLIVE" wrote:

With your date in A1:

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

HTH,
Paul

--

"scott" wrote in message
...
I am working on a spreadsheet that has monthly data for a five-year period.
I
need to convert all the data for each month into daily data. I am simply
dividing the total for each month by the number of days in each respective
month..Instead of copy and paste, or drag and drop, is there a formula
that
allows a user to click on a cell containing the name of a month and have
excel recognize the number of days in that month? For example, if A1 has
"September" and B1 has 1,000,000, can I insert a formula in C1 that allows
me
to do =B1/A1 instead of B1/30?

Sorry for the confusion and thanks for the help.

Month Formula
 

You could use EOMONTH and DAY to get the 28-31 number desired.

scott wrote:

I am working on a spreadsheet that has monthly data for a five-year period. I
need to convert all the data for each month into daily data. I am simply
dividing the total for each month by the number of days in each respective
month..Instead of copy and paste, or drag and drop, is there a formula that
allows a user to click on a cell containing the name of a month and have
excel recognize the number of days in that month? For example, if A1 has
"September" and B1 has 1,000,000, can I insert a formula in C1 that allows me
to do =B1/A1 instead of B1/30?

Sorry for the confusion and thanks for the help.

Month Formula
 

I'm not sure why you would be getting 30. As you know, you should never get
30. As long as you have a valid date including the year in the specified
cell (A1), then the result should be accurate. Try recalculating (F9).



--

"scott" wrote in message
...
Thanks for the formula, but it does not recognize that February has 28/29
days...it returns "30" for all February months in the five-year period.
What
change do I need to make in the formula you provided me to fix this?

"PCLIVE" wrote:

With your date in A1:

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

HTH,
Paul

--

"scott" wrote in message
...
I am working on a spreadsheet that has monthly data for a five-year
period.
I
need to convert all the data for each month into daily data. I am
simply
dividing the total for each month by the number of days in each
respective
month..Instead of copy and paste, or drag and drop, is there a formula
that
allows a user to click on a cell containing the name of a month and
have
excel recognize the number of days in that month? For example, if A1
has
"September" and B1 has 1,000,000, can I insert a formula in C1 that
allows
me
to do =B1/A1 instead of B1/30?

Sorry for the confusion and thanks for the help.