Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to get a Boolean formula to work month-to-month | Excel Discussion (Misc queries) | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
month formula | Excel Worksheet Functions | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) |