Filling a range of cell with months results in duplciates

garwil

New Member
Joined
Jul 18, 2010
Messages
28
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:

Some videos you may like

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
Joined
Jun 17, 2005
Messages
7,764
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Excel Workbook
ABC
1
2
3Jun-11
4
5
6Your
7CurrentTry
8ApproachInstead
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
Joined
May 9, 2009
Messages
16,623
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
Joined
Jul 18, 2010
Messages
28

ADVERTISEMENT

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
Joined
May 9, 2009
Messages
16,623
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,428
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top