Date recognition issue

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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:
VBA Code:
Range("B2:B" & lastRow).NumberFormat = "@"
Range("B2:B" & lastRow).NumberFormat = "MMMYY"

Neither approach makes a difference. Any suggestions?

Book13
AB
1Original in CSVAfter Copy in XLS
2Sep-2020-Sep
3Oct-2020-Oct
4Nov-2020-Nov
5Dec-2020-Dec
6Jan-2121-Jan
7Feb-2121-Feb
8Mar-2121-Mar
9Apr-2121-Apr
10May-2121-May
11Jun-2121-Jun
12Jul-2121-Jul
13Aug-2121-Aug
14Sep-2121-Sep
15Oct-2121-Oct
16Nov-2121-Nov
17Dec-2121-Dec
18Jan-2222-Jan
19Feb-22
Sheet1
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
I am copying my simplified section of VBA code in case you see anything there

VBA Code:
Sub copydata ()

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Dim dTod As String, dYes As String

'Adjust for Monday
If Format(Now(), "DDD") = "Mon" Then
    dTod = Format(Date - 2, "yyyy-mm-dd")
    dYes = Format(Date - 3, "yyyy-mm-dd")
Else

    dTod = Format(Date, "yyyy-mm-dd")
    dYes = Format(Date - 1, "yyyy-mm-dd")
End If

Dim localZipFile As Variant, destFolder As Variant  'Both must be Variant with late binding of Shell object
Dim Sh As Object

Dim strFileName As String
Dim strFileExists As String

'Check the file name ending
strFileName = "\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.zip"
strFileExists = Dir(strFileName)
 
If strFileExists = "" Then     '.zip file to be unzipped
    localZipFile = "\\path\DataY_" & dYes & "_" & dTod & "_000502UTC.zip"
Else
    localZipFile = "\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.zip"
End If
    
destFolder = "\\path\"    'destination folder of .zip file's unzipped contents
    
'Unzip all files in the .zip file
        
Set Sh = CreateObject("Shell.Application")
With Sh
        
.Namespace(destFolder).CopyHere .Namespace(localZipFile).Items
End With

'copy data from closed source workbook
Set myApp = CreateObject("Excel.Application")

'Check the file name ending
 
If strFileExists = "" Then
    Set wkBk = myApp.Workbooks.Open("\\path\DataY_" & dYes & "_" & dTod & "_000502UTC.csv")
Else
    Set wkBk = myApp.Workbooks.Open("\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.csv")
End If
                 
lastRow = wkBk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("C2:H" & lastRow).Copy

myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate
Range("A1").Select
wkSht.Paste

lastRow = Range("A" & Rows.Count).End(xlUp).Row
'change date format

Range("C2:C" & lastRow).NumberFormat = "MMMYY" 'This line makes to difference to the result

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,638
Messages
5,549,111
Members
410,897
Latest member
Ekrupa25
Top