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

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
LVL 1
Edward StevensIT ConsultantAsked:
Who is Participating?
 
Edward StevensIT ConsultantAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Gustav BrockCIOCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Edward StevensIT ConsultantAuthor Commented:
Thanks

I will try these suggestions tomorrow and post my results.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Gustav BrockCIOCommented:
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
 
Edward StevensIT ConsultantAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
That was the answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.