Reputation: 1419
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?
Upvotes: 2
Views: 30040
Reputation: 41
Instead of trying
Cells(row2,col2) = Cells(row1,col1)
Try this
Cells(row1,col1).Copy
Cells(row2,col2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Upvotes: 0
Reputation: 12403
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
Upvotes: 4
Reputation: 18064
Selection.NumberFormat = "mmm-yy"
Above line is changing the number format. Actually When am running through your steps, I have come across the same issue.
But Number format is doing all this auto change.
Example:
While am running the macro, assume that selected cell is (17,3)
.
01/11/2012
"Jan-12"
. (Since number format is applied as "mmm-yy"
, so it consider as 01/11/2012
is consider as Jan-12
)Second time, if you select the cell (17,4)
2/11/2012
"Feb-12"
Upvotes: 0