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?

Tony Dallimore · Accepted Answer · 2012-02-24 11:17:47Z

4

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

answered Feb 24, 2012 at 11:17

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Siva Charan · Accepted Answer · 2012-02-24 06:47:49Z

0

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).
- 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)
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"

answered Feb 24, 2012 at 6:47

Siva Charan

18.1k10 gold badges64 silver badges95 bronze badges

Comments

Glen · Accepted Answer · 2013-06-01 07:49:48Z

0

Instead of trying

Cells(row2,col2) = Cells(row1,col1)

Try this

Cells(row1,col1).Copy
Cells(row2,col2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

answered Jun 1, 2013 at 7:49

Glen

413 bronze badges

Collectives™ on Stack Overflow

Excel VBA - the date format changes automatically

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related