Month Formula

[scott]

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.

 

[PCLIVE]

With your date in A1:

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

HTH,
Paul

 

[scott]

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?

 

[Bob I]

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

 

[PCLIVE]

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



--