Solved

Dates in Excel 2013 Appear One Day Off In Access 2013 Link Table

Posted on 2016-09-30
10
53 Views
Last Modified: 2016-10-25
I have a complex Excel 2013 Spreadsheet that is acting as a linked table in an Access 2013 database to facilitate reporting.

The interesting, but frustrating thing is that the dates in Excel are coming into Access as the day before.  For example; a date in Excel that is 12/20/2016 appears in Access 2013 as 12/09/2016.

Any ideas on how I can get the two applications to show the date as the same value?  i.e. 12/20/2016 in both Excel and Access

Thanks
0
Comment
Question by:Edward Stevens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 20
ID: 41824446
I imagine it might be related to format of the dates as Access and Excel both use the same base date for 1: 12/31/1899

In Excel, try Short Date format for the cells -- this is m/d/yyyy in the states unless the Windows Region settings have been changed.  You can check to see what short date format is for a date in VBA:
    ?format(date(),"Short Date")

Open in new window

in the Debug (Immediate) window and then pressing ENTER (press Alt-F11 to go to code and then Ctrl-G to go to the Debug window)

In Access, the data type should be date/time using Short Date format

Short Date is a special named format that is based on Windows Region settings.  Access recognizes Short Date, and so does VBA, but not cells in Excel so you need to pick a format will be properly understood to minimize issues -- whatever is short date format is a good bet -- often so is yyyy-mm-dd
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41824546
This happens when you in Excel:

    Settings, Advanced, Calculation of this workbook

has marked option: Use 1904 date system.

So either unmark this and adjust your dates in the workbook, or use

    TrueDate: DateAdd("d",-1,[YourDateField])

in a straight select query using the Excel table as source, and then use this query for your further processing and not the linked table.

/gustav
0
 
LVL 1

Author Comment

by:Edward Stevens
ID: 41829038
Thanks

I will try these suggestions tomorrow and post my results.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 20
ID: 41829059
interesting, gustav, that is not a default.  I remember it used to be a problem.  Out of curiosity, do you know why anyone would want to use that?

The 1904 data system option is located about 5 sets of options up from the bottom of the Advanced category when you choose File, Options.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41829390
This dates back to the days of DOS and Lotus 1-2-3.

When Excel arrived, Lotus had total dominance, and often a requirement for a change to Windows and Excel was, that Excel should be able to read a Lotus file right away. And vice versa: Save as WKS/WK1/WK3/WK4

You will only need this setting if you exchange files with a Lotus user - which today is difficult as those file formats can neither be opened nor saved as in Excel. I don't know if another format still supported by Excel uses this 1904 setting. I would say, it could safely be removed.

/gustav
0
 
LVL 1

Author Comment

by:Edward Stevens
ID: 41832236
Hi Gustav,

Confirmed that the 1904 setting is already unchecked.

Interesting to note:  I assumed that this would be a "consistent" problem but oddly it is not.

On the affected machine, laptop A, 1904 is unchecked in Excel 2013 and the problem exists.

On laptop B, 1904 is unchecked in Excel 2013 and the problem does n exist.

I am now playing with the date formats as suggested by crystal to see if that sheds any light on this issue.

I will post my findings shortly.
0
 
LVL 1

Accepted Solution

by:
Edward Stevens earned 500 total points (awarded by participants)
ID: 41832539
Sorry for the question.  Is there a way to give you folks points without marking the responses as solutions?  I think you deserve credit for your time but it turns out that this question was a result of my own stupidity.

Someone else designed the Access database who I "thought" was non-technical and I did not do my diligence when troubleshooting the issue.

The dates are off because the date column was not a direct link to the field but rather a piece of VBA code that was altering the dates prior to printing them on the report.

I guess the person was more technical than I gave them credit for.  My bad.
0
 
LVL 20
ID: 41832927
you can mark your post as the answer and give us points for assist -- but Gustav would know better than me since he has been here longer.  Glad you got it figured out ~
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41833054
Oh, that explains the mystery. Thanks for the feedback.

I believe you can select multiple answers. Mark your own as "Best" and the others as "Assisting" (can't recall the exact phrase). Then adjust points as you like. If that doesn't work out, click the Request Attention link at bottom-right of your question box.

/gustav
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41858237
That was the answer.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question