MonthName to Integer from Current to Previous Year

[nerbe]

I am trying to convert a Month's name to an integer in order to do
trending for the previous six months.

I am taking the string value MonthName() and while looping through a
folder's subfolders am comparing the MonthName to (part of) the
subfolder name. If there is a match, I open the folder and the
workbooks contained therein.

The problem is that I am trying to figure out a way to go back to the
previous year using the following:

'Set Current Date.
dt = DateTime.Date

'Break up the date.
m = DateTime.Month(dt)
d = DateTime.Day(dt)
y = DateTime.Year(dt)

'Prior month's names.
pm1 = MonthName(m - 1)
pm2 = MonthName(m - 2)
pm3 = MonthName(m - 3)
pm4 = MonthName(m - 4)
pm5 = MonthName(m - 5)
pm6 = MonthName(m - 6)
pm7 = MonthName(m - 7)

The current month (April 2006) = 4. When I subtract and finally get to
December 2005, the integer value = 0. November 2005 would theoretically
be -1, October would be -2, September would be -3, and so on.

Does anyone know how I can do this?

Thanks in Advance!

 

[Bob Phillips]

I am not sure that I fully understand, but I think you only need

'Set Current Date.
dt = Date

'Break up the date.
m = Month(dt)

'Prior month's names.
pm1 = MonthName(m - 1)
pm2 = MonthName(m - 2)
pm3 = MonthName(m - 3)
pm4 = MonthName(m - 4)
pm5 = MonthName(m - 5)
pm6 = MonthName(m - 6)
pm7 = MonthName(m - 7)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[nerbe]

Sorry. I should have clarified...

The way I am currently calculating this is essentially 4-7 = -3. That
is, (April 2006) - (7 months) = October 2005

October is the 10th month of the year, but in doing the math the way I
am currently doing it, I get a negative number -- a number that does
not correspond to a month 1 thru 12.

 

[Bob Phillips]

Do something like

dt = Date
m = Month(dt)
m = m - 4
If m <= 0 Then m = m + 12


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)