Simple Date formula

[HotNumbers]

I Have a small brain block today. Can someone please help me write a simple formula

I am trying to take a date and go back few months from it and also few months ahead.

In Cel A3 I have the following date: 04/14/06 I need a formula going accross in B3,C3,D3, etc. that will calculate less then 6 month, less then 5 month, less then 4 months, .... to Plus 2 months.

Any one have any ideas.

 

[NBVC]

Try:


In B3:

=DATE(YEAR($A$3),MONTH($A$3)-COLUMN($M1)+COLUMN(G1),DAY($A$3)) copied over

 

[HotNumbers]

NBVC,

It is not working. I am trying to post my Sheet to the site so that you can see what I am trying to do

 

[HotNumbers]

Here is what part of my Sheet looks like

Book6
	A	B	C	D	E	F	G	H	I	J	K
3
4			-6	-5	-4	-3	-2	-1	0	1	2
5	Release Date	Month	M -6	M -5	M -4	M -3	M -2	M -1	M 0	M +1	M +2
6	4/14/2006	4	3/14/2006	3/14/2005	2/14/2005	1/14/2005	12/14/2004	11/14/2004	10/14/2004	9/14/2004	8/14/2004
7	8/15/2006	4
8	12/12/2006	4
9
Sheet1

 

[barry houdini]

I'm not sure I understand what you want. Why is C6 a month before A6 but then D6 is a year before C6?

In general to deduct months from a date you can use EDATE (available with Analysis ToolPak add-in), so to subtract 6 months from a date in A6

=EDATE(A6,-6)

......Although you may need to decide what date you want if you go back 6 months from a date like 31st August

 

[HotNumbers]

Thank you

 

[NBVC]

Just want to show that my original formula does work per your original description of the problem...see formulas in row 6 (I also include the formula using EDATE() as mentioned by Barry...which gives same results)

Book3Oct20.xls
	A	B	C	D	E	F	G	H	I	J	K	L
4
5			-6 Months	-5 Months	-4 Months	-3 Months	-2 Months	-1 Months	-0 Months	+1 Months	+2 Months
6	4/14/2006		10/14/2005	11/14/2005	12/14/2005	1/14/2006	2/14/2006	3/14/2006	4/14/2006	5/14/2006	6/14/2006
7			10/14/2005	11/14/2005	12/14/2005	1/14/2006	2/14/2006	3/14/2006	4/14/2006	5/14/2006	6/14/2006
8
Sheet1