8
votes

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")
2

2 Answers

19
votes

Just format the existing date directly, ie

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

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