creating dates in the past with given date

[dexelguy]

Hi, I have two cells with data like following:

A B c
06/11/2006 1 ??
The cell B will have values like 1 (for Daily), 2 (for Weekly), and 3 (for Monthly).
Depending upon the value of Cell B, I need to go -10 in the past and populate 10 cells with those dates. For example:

A B c D ... L
06/11/2006 1 06/10/2008 06/09/2008 ...06/02/2008

Similarly if Cell B=2, I need to go -10 weeks and populate cells C to L with those dates and similarly for Cell B=3, -10 months.

Any ideas how can this be done? If my question is not clear, please let me know immediately. thanks in advance.

[NBVC]

If your date is in A1 and the code is in B1, enter this in C1 and copy across to L1:

=CHOOSE($B$1,$A$1-COLUMN(A$1),$A$1-7*COLUMN(A$1),DATE(YEAR($A$1),MONTH($A$1)-COLUMN(A$1),DAY($A$1)))

[daddylonglegs]

Assuming A2 has start date and B2, 1, 2 or 3 you could use this formula in C2 copied across to L2

=IF($B2="","",CHOOSE($B2,$A2-COLUMNS($C2:C2),$A2-COLUMNS($C2:C2)*7,EDATE($A2,COLUMNS($C2:C2))))

Note: EDATE requires Analysis ToolPak add-in.......

[NBVC]

Assuming A2 has start date and B2, 1, 2 or 3 you could use this formula in C2 copied across to L2

=IF($B2="","",CHOOSE($B2,$A2-COLUMNS($C2:C2),$A2-COLUMNS($C2:C2)*7,EDATE($A2,COLUMNS($C2:C2))))

Note: EDATE requires Analysis ToolPak add-in.......

Hmmm... close! not bad, eh?!

[dexelguy]

Hi guys, thanks for your replies. I applied NBVCs formula. It is showing 06/10/2008 in all 10 cells. I have date in cell A1 and values 1,2,3 in cell A2. My display cells are C11 to L11. May be I didn't copy it right. Please advise. Thanks.
This is what I put in C11 to L11
=CHOOSE($A$2,$A$1-COLUMN(A$1),$A$1-7*COLUMN(A$1),DATE(YEAR($A$1),MONTH($A$1)-COLUMN(A$1),DAY($A$1)))

[NBVC]

Check that you have calculations set to automatic...

Go to Tools|Options and then Calculation tab... the Automatic radio button should be selected.

[dexelguy]

Yes, that automatic option is selected.

[NBVC]

So you copied the formula only to C11, then you clicked on the little black square at the bottom right corner of C11, held the mouse button down and dragged the formula across to D11?

[dexelguy]

ummm. i guess i did not do that. i just copied the exact same formula from the top bar and pasted it in each cell. let me try again with what you just mentioned.