Access data display wrong date on Excel

bokboxes

New Member
Joined
Apr 18, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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?

1619759738873.png
1619759844785.png


Please help!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,425
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Looks like a leap year issue maybe. Double check your Cell formatting.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,425
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 18, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,425
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Google "Dates in excel are off by 4 years and 1 day" ;)
 
Solution

Forum statistics

Threads
1,143,638
Messages
5,719,979
Members
422,254
Latest member
sayedsaid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top