DataWright Information Services

Consulting and Resources for Excel and Access

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

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