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]).

  Home | About | Links | Contact | Excel | Access