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:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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)
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top