Excel VBA - the date format changes automatically

Question

I'm trying to enter the Date value by adding a month to the date in Sheets("Sheet1").Cells(17, 3).Value which is 01/10/2011 but format as Oct-11. Then return in Sheets("Sheet1").Cells(17, 4).Value = LDate, to show Nov-11

sDate = Sheets("Sheet1").Cells(17, 3).Value   --> this shows 01/10/2011 when I hove over sDate
LDate = DateAdd("m", 1, sDate)                --> this shows 01/11/2011 when I hove over LDate

I then want to enter that value 01/11/2011 in to the following cell

Sheets("Sheet1").Cells(17, 4).Value = LDate
Selection.NumberFormat = "mmm-yy"

But in the cell it shows 11/01/2011 (Jan-11), why is it doing this and how can I fix this issue?

Solution

Minor issue

Selection.NumberFormat = "mmm-yy"

You have not selected anything so this format is placed wherever you left the cursor.

Major issue

You have hit the Excel bug that it will interpret dates as being in American (middle endian) format if it can when transferring data to a worksheet. "1/11/2011" would be "11 Jan 11" to an American so it is to Excel. "20/11/2011" is not a valid American date so to Excel it is "20 Nov 11".

I can duplicate your problem by declaring sDate and LDate as strings. DateAdd works correctly with strings so LDate is correct but when placed in a cell it is misinterpreted.

I can fix your problem by declaring sDate and LDate as dates:

Dim sdate As Date
Dim Ldate As Date