Excel VBA format (<Date>, "mmmmm-yy") does not work properly

Question

I have cells in my excel sheet that contain a date.

The display format for these cells is the custom format “mmmmm-yy” so the date “7/1/16” appears as “S-16”. This working format works on an excel sheet, however in VBA I try to call the Format () function in these cells, it does not give the same format.

Format( <Date_Cell> , "mmmmm-yy")

Gives

"April4-16"

on a chart, for example.

Why Formatdoesn't the function behave the same as formatting a date cell?

Edit: According to the support website, format the date the way you want , I use the correct format, but this is not the result of which the site claims that it should.

Edit2: , , VBA Format, .

Answer 1

VBA Excel 100%.

TEXT, :

Application.Text( <Date_Cell> , "mmmmm-yy")

.

Answer 2

VBA Format mmmmm (. ). "mmmm" ( ), "m" ( ). , :

Left$(MonthName(Month(<Date_Cell>)), 1) & "-" & Right$(CStr(Year(<Date_Cell>)), 2)

Answer 3

Another option:

Change the cell number format:

Range("A1").NumberFormat = "mmmmm-yy"