Solved

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

Posted on 2016-09-30
10
48 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

821 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