Excel 2007: Count backwards by month given a starting month

Question

I am working in Excel 2007. My preference would be to do this without VBA. I am trying to count backwards a number of months starting from a given date. For example:

Start Date: July, 2010

Countdown: 12 months

Should result in: Jun 2010 May 2010 Apr 2010 Mar 2010 Feb 2010 Jan 2010 Dec 2009 Nov 2009 Oct 2009 Sep 2009 Aug 2009 Jul 2009

So the seed month is month 0 and the countdown period can vary. I would like the month/year combination for display purposes but I also need the calculated value (ie, seed month is 0, next previous month is -1, etc) for calculations.

Any advice/help would be most appreciated!

Answer 1

It's relatively simple. For example, in cell A1, put "July 2010". In B1, put =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) and in C1 put either =DATE(YEAR(A1),MONTH(A1)-2,DAY(A1)) or =DATE(YEAR(B1),MONTH(B1)-1,DAY(B1)) and so on.

If you're looking for a for/each type of statement with the number of months in the countdown, you'll need to go to VBA. Otherwise, you can prepopulate a range of cells with code like the above (You can start it with an if statement if the countdown cell has nothing, to display nothing).