Solved

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

Posted on 2016-09-30
10
35 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 19
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 49

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
 
LVL 19
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 49

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 19
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 49

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 49

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

919 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now