Inconsistent results with date values

[Yamezz]

I have the code:

Dim InvDate As Date

InvDate = Sheets("Sale").Range("SaleDate").Value
InvDate = Format(InvDate, "dd/mm/yyyy")
MsgBox InvDate
MsgBox Month(DateAdd("m", -1, InvDate))
MsgBox Format(Month(DateAdd("m", -1, InvDate)), "mmm")

which, when supplied the date 01-Sep-14, gives different results for my msgbox lines.
MsgBox InvDate returns "1/09/2014", which is correct.
MsgBox Month(DateAdd("m", -1, InvDate)) returns "8", which is correct.
However, MsgBox Format(Month(DateAdd("m", -1, InvDate)), "mmm") returns "Jan".
It seems in the last line the date format has reverted back to the illogical American format.

How can this be? I need the returned value to be "Aug".
Thanks Guys.

 

[Michael M]

Maybe this

Sub MM1()
Dim InvDate As Date

InvDate = Sheets("Sale").Range("SaleDate").Value
InvDate = Format(InvDate, "dd/mm/yyyy")
MsgBox InvDate
MsgBox Format(InvDate - 1, "m")
MsgBox Format(InvDate - 1, "mmm")
End Sub

 

[Yamezz]

Thanks Michael,

That works for 1st September, but I also need it to work for other days in say September - i.e. with all dates assigned to InvDate I would like to get the previous month, hence why I was using DateAdd("m" -1).

Is there a way to convert the "8" output of Month(DateAdd("m", -1, InvDate)) to "Aug"? Could this be the easy way out?

 

[Michael M]

Maybe this

Sub MM1()
invdate = Format(DateAdd("m", -1, Sheets("Sale").Range("SaleDate").Value), "mmmm")
MsgBox invdate
End Sub

 

[Yamezz]

Thanks Michael. That's it.

 

[Michael M]

Glad it worked....