formula for headings in columns

T

thrava

OK, say I have 24 columns which represents 24 months.

The last column contains data from the latest available
month-year. Say Jul 04.

I like to be able to use formulae in all of these 24
column headings so that when I type the date of latest
data (say in column Z1), that all columns headings will be
automatically displaying the correct month-year.

For Example if I type Jul 04 in cell Z1, I want
Column24.Row 1 to say Jul 04
column 23, Row 1 to say Jun o4
..
..
..
Column 1 would say Jul 03
What formula do you use for this?
Thanks
Thrava
 
J

JE McGimpsey

One way:

A1: =DATE(YEAR($Z$1),MONTH($Z$1)-(24-COLUMN()),1)

formatted with Format/Cells/Number/Custom mmm yy

Note: Column 1, given your Example, would result in "Aug 02" not "Jul 03"
 
B

Biff

Hi!
Column 1 would say Jul 03
Click to expand...

If you have 24 months of data and the last month of data
is from Jul 04 then col 1 will be Aug 02!

If you enter in Z1 as a real Excel date: Jul 04

In col 24 (X1) enter: =Z1

Now goto col 1 (A1) enter: =B1-30

Now just copy across to W1.

Not real robust in that it doesn't account for leap years.

Biff
 
Top