Access data display wrong date on Excel

[bokboxes]

Hi guys, I have my access data export to excel with the codes below,



Sub Refresh()

Const cstrPath As String = "P:\Common\Operation\Folder A\Combined Data.accdb"
Const cstrQuery As String = "Final Data"
Dim dbe As Object 'DAO.DBEngine '
Dim rs As Object 'DAO.Recordset '
Dim ws As Worksheet

Application.DisplayAlerts = True 'leave alerts on during testing '
Set dbe = CreateObject("DAO.DBEngine.120")
Set rs = dbe.OpenDatabase(cstrPath).OpenRecordset(cstrQuery)

If Not rs.EOF Then
Set ws = ThisWorkbook.Sheets("Test")
ws.Range("A1").CopyFromRecordset rs
End If

rs.Close
Application.DisplayAlerts = True


End Sub


But, dates are display wrongly. Dates that are 2021 are now all 2025 and days are 1 day ahead.
Where is it wrong?

Please help!

 

[johnnyL]

Looks like a leap year issue maybe. Double check your Cell formatting.

 

[johnnyL]

When you copy data between two workbooks that use different systems, you'll run into shifting dates. The easiest way to adjust the dates is to use the Paste Special option to add or subtract 1,462 to each date:

• Add 1,462 to shift dates up four years and one day.
• Subtract 1,462 to shift dates down four years and one day.

 

[bokboxes]

When you copy data between two workbooks that use different systems, you'll run into shifting dates. The easiest way to adjust the dates is to use the Paste Special option to add or subtract 1,462 to each date:

• Add 1,462 to shift dates up four years and one day.
• Subtract 1,462 to shift dates down four years and one day.

The access data is always update with new rows. macro button is assigned in excel sheet to copy the same new rows.
And, I'm not familiar with paste special function. Could you provide guidance?

 

[johnnyL]

Google "Dates in excel are off by 4 years and 1 day"

 

[bokboxes]

Google "Dates in excel are off by 4 years and 1 day"

I've uncheck the date system in option. Thanks !