How can I format a month in mmm format?

Question

I am trying to format a date in Excel with VBA, the current month to be in mmm format. Somehow I am getting the previous month, instead of the current month. I have checked and my computer month is Feb, but I am getting Jan, instead.

This is my code:

Cells(1, 2) = Format(month(Date), "mmm")

Answer 1

Just format the existing date directly, ie

Cells(1, 2) = Format(Date, "mmm")

Answer 2

You are formating the date 1/1/1900

Let me explain:

• Date is worth 21/02/2012
• Month(Date) is worth 1
• Format will consider this as a date so Month(Date) will be 1/1/1900
• So Format(month(Date), "mmm") will return Jan as the month of 1/1/1900

To teach you fishing, when you face this kind of issue, you can try to find the value of each statement :

• Debug.Print (Date) returns 21/02/2012 so this part is ok
• Debug.Print (Format(Month(Date), "mmm")) returns jan so this is not a displaying cell issue