Fill in months back automatically

jonasmckee

New Member
Joined
Mar 27, 2002
Messages
1
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..
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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