Excel VBA Format(<Date>, "mmmmm-yy") not working as expected

Question

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

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

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

Gives

"April4-16"

on the chart, for example.

Why does the Format function not behave the same as formatting a date cell?

Edit: According to the office support website, Format a date the way you want, I am using the correct format, but it is not producing the result the website claims it should.

Edit2: Turns out cell date formatting works differently than the VBA Format function, as the selected answer points out.

Answer 1

The meaning of the format symbols of the VBA function Format and the Excel Date Format are not 100% identically.

You could use the worksheet function TEXT like:

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

to achieve what you want.