Solved

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

Posted on 2016-09-30
10
29 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 18
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 18
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 18
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

13 Experts available now in Live!

Get 1:1 Help Now