Date Subtraction

J

John Calder

Hi

I run Win2K with Excel 2K.

I would like to enter a date in a cell (eg Oct-05) and have the preceding 11
cells automatically put the previous months in.

Example:

In cell A15 = Oct-05 (entered)
In cell A14 = Sep-05 (automatically)
In cell A13 = Aug-05 (automatically)
In cell A12 = Jul-05 (automatically)

etc etc etc

Is there a formula that can do this?

Any help will be much appreciated...thanks !

John
 
R

Ron Rosenfeld

Hi

I run Win2K with Excel 2K.

I would like to enter a date in a cell (eg Oct-05) and have the preceding 11
cells automatically put the previous months in.

Example:

In cell A15 = Oct-05 (entered)
In cell A14 = Sep-05 (automatically)
In cell A13 = Aug-05 (automatically)
In cell A12 = Jul-05 (automatically)

etc etc etc

Is there a formula that can do this?

Any help will be much appreciated...thanks !

John
Click to expand...

A14: =edate($A$15,-CELL("row",$A$15)+ROW())

and drag/copy UP to A12

----------------------------
If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
 
D

Dave Peterson

How about just putting a formula in A14:A4?

Put this in A14 and drag up:
=IF(A15="","",DATE(YEAR(A15),MONTH(A15)-1,1))

I used the first of month for my date--is that ok?
 
J

John Calder

Thanks Guys ! - Well Done !

Dave Peterson said:
How about just putting a formula in A14:A4?

Put this in A14 and drag up:
=IF(A15="","",DATE(YEAR(A15),MONTH(A15)-1,1))

I used the first of month for my date--is that ok?
Click to expand...
 
Top