Fill in months back automatically

[jonasmckee]

I am need of a way for the cells in row 1 to fill in the dates automatically backwards for 13 months. For example..
At cell N1 the user would put in a Date (3/15/02), then cells M1 backwards to A1 would then fill in the dates (M1 would equal N1 minus one month, L1 would equal N1 minus one month, etc.)
Any help would be aprrectiated please..

[Dave Patton]

in N1 3/15/02 or 15/03/02 depending on how your system is set up (edited)

in M1 =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))
copy M1 to L to A

Set references to absolute if all months
are to be - 1.

Format the cells so the information fits.

[ This Message was edited by: Dave Patton on 2002-03-28 19:59 ]

[ This Message was edited by: Dave Patton on 2002-03-28 20:01 ]

[Dave Hawley]

Hi

You can also use the EDATE Function for this. Let's say the date is in cell A1

=EDATE(A1,-1)

Just copy this across. The EDATE is part of an Excel Add-in so before trying, go to Tools>Add-ins and check the "Analysis toolpak"

[Yogi Anand]

On 2002-03-28 19:37, Dave Patton wrote:

in N1 15/03/02
in M1 =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))
copy M1 to L to A

Set references to absolute if all months
are to be - 1.

Format the cells so the information fits.

Hi Dave Patton:
In N1 ... 3/15/02 and not 15/3/02

Funny thing is Excel will accept an entry like 15/3/02 as a text entry and fool one into thinking every thing is OK!

[ This Message was edited by: Yogi Anand on 2002-03-28 19:54 ]

[ This Message was edited by: Yogi Anand on 2002-03-28 19:55 ]

[Dave Patton]

Quote
" Funny thing is Excel will accept an entry like 15/3/02 as a text entry and fool one into thinking every thinking is OK!"

Sorry about that, my system is set ddmmyyyy and my editor is nearly asleep. I should not do my own editing.

The type does not change the suggestion.

You have a major challenge if your are about to edit the spelling and English in these newsgroups!

[Yogi Anand]

Hi Dave Hawley:
That's Great ... Short and Sweet!

[gypo]

On 2002-03-28 19:48, Dave Hawley wrote:
Hi

You can also use the EDATE Function for this. Let's say the date is in cell A1

=EDATE(A1,-1)

Just copy this across. The EDATE is part of an Excel Add-in so before trying, go to Tools>Add-ins and check the "Analysis toolpak"

But, small note :-

If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results ..... but then neither will the formula =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))




[ This Message was edited by: gypo on 2002-03-28 21:19 ]

[Dave Hawley]

Hi

RE:If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results

Maybe you should try it and you will see it does.

[gypo]

On 2002-03-28 22:50, Dave Hawley wrote:
Hi

RE:If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results

Maybe you should try it and you will see it does.

I would suggest that you're the one who hasn't tried it.

Put 30 November 2002 in cell A1.
Put =EDATE(A1,-1) in A2 and drag down.

A3 will show 30 Oct (normally the 31st is the last day of October, isn't it?), and the day in each cell below will be 30 until Feb when it will be 28. Thereafter, the day in each cell will be 28.

[BobUmlas]

MUCH easier way:
In N1 enter your date. Then right-click the fill handle and drag left to cell B1, for example. When you let go, you'll see a dropdown which enables you to choose "Fill Months". They will be backwards, as you wanted.