# Filling a range of cell with months results in duplciates

#### garwil

##### New Member
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?.

Last edited:

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### njimack

##### Well-known Member
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)

Excel Workbook
ABC
1
2
3Jun-11
4
5
6Your
7CurrentTry
9Jun-11Jun-11
10Jul-11Jul-11
11Jul-11Aug-11
12Aug-11Sep-11
13Sep-11Oct-11
14Oct-11Nov-11
15Nov-11Dec-11
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
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

##### New Member
Thanks a ton biff.. much appreciated..

#### T. Valko

##### Well-known Member
Thanks a ton biff.. much appreciated..
You're welcome. Thanks for the feedback! Replies
3
Views
27
Replies
9
Views
88
Replies
8
Views
64
Replies
1
Views
45
Replies
9
Views
625