Hi all,
I am running a VBA code which instructs to copy data from .csv file to .xls file.
Without any transformation to the dates, the year in the original data is suddenly recognized as the day of the month once copied (refer to the table below). I tried clearing formatting of the .xls file to ensure it does not affect the data copied into it. Also instructed to change the date format to the following:
Neither approach makes a difference. Any suggestions?
I am running a VBA code which instructs to copy data from .csv file to .xls file.
Without any transformation to the dates, the year in the original data is suddenly recognized as the day of the month once copied (refer to the table below). I tried clearing formatting of the .xls file to ensure it does not affect the data copied into it. Also instructed to change the date format to the following:
VBA Code:
Range("B2:B" & lastRow).NumberFormat = "@"
Range("B2:B" & lastRow).NumberFormat = "MMMYY"
Neither approach makes a difference. Any suggestions?
Book13 | ||||
---|---|---|---|---|
A | B | |||
1 | Original in CSV | After Copy in XLS | ||
2 | Sep-20 | 20-Sep | ||
3 | Oct-20 | 20-Oct | ||
4 | Nov-20 | 20-Nov | ||
5 | Dec-20 | 20-Dec | ||
6 | Jan-21 | 21-Jan | ||
7 | Feb-21 | 21-Feb | ||
8 | Mar-21 | 21-Mar | ||
9 | Apr-21 | 21-Apr | ||
10 | May-21 | 21-May | ||
11 | Jun-21 | 21-Jun | ||
12 | Jul-21 | 21-Jul | ||
13 | Aug-21 | 21-Aug | ||
14 | Sep-21 | 21-Sep | ||
15 | Oct-21 | 21-Oct | ||
16 | Nov-21 | 21-Nov | ||
17 | Dec-21 | 21-Dec | ||
18 | Jan-22 | 22-Jan | ||
19 | Feb-22 | |||
Sheet1 |