By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

I have a problem running Format in A97

P: n/a
MLH
If I run this...
For i = -1 To -12 Step -1
Debug.Print CStr(Month(DateAdd("m", i, Now))) & "/";
CStr(Year(DateAdd("m", i, Now)))
Next i

I get this...
5/2005
4/2005
3/2005
2/2005
1/2005
12/2004
11/2004
10/2004
9/2004
8/2004
7/2004
6/2004

which is exactly what I want, except for one thing -
I would like to see month abbrev replace the numbers.
So, I run this...

For i = -1 To -12 Step -1
Debug.Print Format(Month(DateAdd("m", i, Now)), "mmm") & "/";
CStr(Year(DateAdd("m", i, Now)))
Next i
But, unfortunately, it returns this...
Jan/2005
Jan/2005
Jan/2005
Jan/2005
Dec/2005
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004

Am I missing something here?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"MLH" <CR**@NorthState.net> wrote in message
news:di********************************@4ax.com...
[snip]
I would like to see month abbrev replace the numbers.
So, I run this...

For i = -1 To -12 Step -1
Debug.Print Format(Month(DateAdd("m", i, Now)), "mmm") & "/";
CStr(Year(DateAdd("m", i, Now)))
Next i
But, unfortunately, it returns this...
Jan/2005
Jan/2005
Jan/2005
Jan/2005
Dec/2005
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004
Jan/2004

Am I missing something here?


Yes, the expression...

Format(Month(DateAdd("m", i, Now)), "mmm")

....is incorrect because to format something as "mmm" you must provide a first
argument that is a date and the Month() function returns an integer. Since
dates are actually Doubles under the covers you are actually asking to format a
date that occurs in the first 12 days of the year 1900. Just remove the Month()
function from your expression and it should work.

Actually all you need is...

Format(DateAdd("m", i, Now), "mmm/yyyy")

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #2

P: n/a
MLH
Many thx, Rick. That was certainly my problem.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.