Transferring Times From Excel To Access
Application: Access and Excel
I recently came across an annoying problem when transferring dates and
times from Excel to Access. I had created a list of time intervals varying
from 0 to about 2 weeks, formatted them as dates, and imported them into
Access with the accompanying table. Trouble is, Access starts its calendar
one day earlier than Excel. Although the dates looked the same, the
underlying values were one day higher than the originals, for all time
intervals greater than one day. As these times formed the basis of a
scheduling application, that was a problem.
First workaround: Next idea was to format the intervals in General format in Excel, import
the numbers and convert them to dates during import. No good. All but about
10 of the records generated errors.
Second workaround: The data was imported into a field formatted as Double (double-precision
floating point, the default number format in Excel, also compatible with
dates), and I used an
expression in an Update query to convert the imported values to dates. Then
something really strange happened. The number 6.58333 converted correctly to
5-Jan-1900 14:00 but the number 6.25 converted to 6:25 (on 31-Dec-1899).
This also happened to 5.25, 4.25, 15.25…
Dates are really just numbers with a special format. In Excel, if you
create a date and time and then convert it to General format, you will see a
number with a decimal. The integer portion of the number represents the
date; the decimal is the fraction of a single day, and represents the time.
So, you would expect .25 to display as 6:00 AM and .75 to display as 6:00
PM.
The solution: Because Access was misinterpreting the imported values, the solution was
to create a custom
function that converts any Double value to its corresponding date value.
This gives the expected time interval, every time.
The code
Function DblToDate(dblValue As Double) As Date Dim dblInterval As Double Dim lngDays As Long Dim intHours As Integer Dim intMinutes As Integer 'convert value to minutes dblInterval = dblValue * 24 * 60 'grab day value lngDays = dblInterval 1440 'grab hour value intHours = (dblInterval Mod 1440) 60 'grab minute value intMinutes = (dblInterval Mod 1440) Mod 60 DblToDate = lngDays + TimeSerial(intHours, intMinutes, 0) End Function
Using the code
To use, paste into a code module and create a query expression like
NewDate:DblToDate([SomeNumberField]).
