Filling a range of cell with months results in duplciates

[garwil]

Hi,
Am filling a column (A9-A20) with months. Cell "A9" is derived from another cell with the formula =C3 (which in this case is June-11)
When I enter into cell "A10" the formula =A9+30.4 I get a result of Jul-11 which is correct however when I enter into cell "A11" the formula =A10+30.4 or even copy the formula down by either cut and paste or using the grab handles and dragging down, instead of a result of Aug-11 cell "A11" repeats as Jul-11. It is only this one month that repeats then the sequence picks up again after the second Jul-11. I had this range copied from another workbook which is working perfectly but not in this new workbook...

I am really having lots of "funnies" with excel and am now wondering if there is something wrong with my version (2007).

Has anyone else experienced this before and if so what was the solution?.

 

[njimack]

If you're trying to increment dates, simply adding 30 isn't the way to go. Something like this:
=DATE(YEAR(A9),MONTH(A9)+1,1))
(assuming A9 is the first of the month, i.e 01/01/11)

 

[jim may]

Excel Workbook
	A	B	C
1
2
3			Jun-11
4
5
6	Your
7	Current	Try
8	Approach	Instead
9	Jun-11	Jun-11
10	Jul-11	Jul-11
11	Jul-11	Aug-11
12	Aug-11	Sep-11
13	Sep-11	Oct-11
14	Oct-11	Nov-11
15	Nov-11	Dec-11
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A9	=C3
A10	=A9+30.4
A11	=A10+30.4
A12	=A11+30.4
A13	=A12+30.4
A14	=A13+30.4
A15	=A14+30.4
B9	=A9
B10	=EDATE(B9,1)
B11	=EDATE(B10,1)
B12	=EDATE(B11,1)
B13	=EDATE(B12,1)
B14	=EDATE(B13,1)
B15	=EDATE(B14,1)

 

[T. Valko]

Hi,
Am filling a column (A9-A20) with months. Cell "A9" is derived from another cell with the formula =C3 (which in this case is June-11)
When I enter into cell "A10" the formula =A9+30.4 I get a result of Jul-11 which is correct however when I enter into cell "A11" the formula =A10+30.4 or even copy the formula down by either cut and paste or using the grab handles and dragging down, instead of a result of Aug-11 cell "A11" repeats as Jul-11. It is only this one month that repeats then the sequence picks up again after the second Jul-11. I had this range copied from another workbook which is working perfectly but not in this new workbook...

I am really having lots of "funnies" with excel and am now wondering if there is something wrong with my version (2007).

Has anyone else experienced this before and if so what was the solution?.

What is the full value of C3? June-11 could have the full value of anywhere from 1-June-11 to 30-June-11.

What is the significance of adding 30.4 to the previous data?

 

[garwil]

I am using an old spreadsheet with 2007. I think it was from an Excel version 4 and it works fine and still does on one of my workbooks. The problem just started with this new workbook. I just assumed that the formula adds one month to whatever is in the preceding cell and copied it across to this new workbook. Going by the replies to this query I have been mistaken and am now using the formula provided by njimack...
thanks to all for their input...

 

[T. Valko]

I am using an old spreadsheet with 2007. I think it was from an Excel version 4 and it works fine and still does on one of my workbooks. The problem just started with this new workbook. I just assumed that the formula adds one month to whatever is in the preceding cell and copied it across to this new workbook. Going by the replies to this query I have been mistaken and am now using the formula provided by njimack...
thanks to all for their input...

If you want the 1st of the month dates...

A9 = some date

Enter this formula in A10 and copy down to A20:

=EOMONTH(A9,0)+1

Format as Date

Note that the EOMONTH function requires the Analysis ToolPak add-in be installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EOMONTH function. It'll tell you how to fix the problem.

 

[garwil]

Thanks a ton biff.. much appreciated..

 

[T. Valko]

Thanks a ton biff.. much appreciated..

You're welcome. Thanks for the feedback!