CODEWITHSUNDEEP
dateexcelvba
HL8
HL8

Reputation: 1419

Excel VBA - the date format changes automatically

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: 30041

Answers (3)

Glen
Glen

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

Tony Dallimore
Tony Dallimore

Reputation: 12413

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

Siva Charan
Siva Charan

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:

  1. While am running the macro, assume that selected cell is (17,3).

    • Before running macro: cell value is 01/11/2012
    • After running macro: cell value will be changed/formatted as "Jan-12". (Since number format is applied as "mmm-yy", so it consider as 01/11/2012 is consider as Jan-12)

  2. Second time, if you select the cell (17,4)

    • Before running macro: cell value is 2/11/2012
    • After running macro: cell value will be changed / formatted as "Feb-12"

Upvotes: 0

Related Questions