Excel VBA-Date format "mmm" not working as expected

[sbishops]

i have some code that seems simple enough but is giving the wrong result and i'm going batty:

If Month(Date) = 1 Then
    myMonth = Format(Month(Date) + 11, "mmm")
Else
    myMonth = Format(Month(Date) - 1, "mmm")
End If

For January, myMonth should be "Dec", but it keeps showing up as "Jan".
How and why?

much thanks!
steph

 

[SkipVought]

Hi,

Format only works on a DATE not MONTH

    myMonth = Format(DateSerial(Year(Date),Month(Date) + 11,Day(Date)), "mmm")

This adds 11 months to the current date.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Let's dissect your formula a bit.

myMonth = Format(Month(Date) + 11, "mmm")
[tab]->
myMonth = Format(Month(1/23/2008) + 11, "mmm")
[tab]->
myMonth = Format(1 + 11, "mmm")
[tab]->
myMonth = Format(12, "mmm")

So for the date, you are providing 12. How does Excel perceive that input? As January 12, 1900. (See faq68-5827 for more information on how Excel deals with Dates and Times.)

I'd suggest simplifying the whole thing. There is no need to treat January differently than any other month. Just subtract actual DATES.

Example:

myMonth = Format(Date - Day(Date), "mmm")

Day(Date) will return an integer equal to the day-of-the-month of Date.

So if Date is 1/23/2009, then Day(Date) will return 23.

1/23/2009 minus 23 = 12/31/2008.

The Month will report back December.

That code will work for any month, so there's no reason for an IF statement.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.