30/12/1899 Error when importing Time from Excel to Access

[Jack]

Hi Access Stars,

I was trying to import data from Excel to Access. I got one colume on my
excel spreadsheet recording the time information as below:

Exam Time Final
9:00:00 AM
9:00:00 AM
9:00:00 AM

After I imported the data to Access, it becomes:

Exam Time Final
30/12/1899 9:00:00 AM
30/12/1899 9:00:00 AM
30/12/1899 9:00:00 AM


Any idea how to get rid of 30/12/1899 and where it comes from? Thanks!

 

[Roger Carlson]

Both Access and Excel store dates as double-precision numbers. The "whole
number" part represents the day (starting with 1/1/1900) and the decimal
portion represents the time. Day 1 is 1/1/1900. Day 0 (zero) is
12/31/1899. Internally, #30/12/1899 9:00:00 AM# is stored as -0.375. I
don't know why your dates are being imported with a negative.

In order to see what's going on, ON A COPY OF YOUR TABLE, change the
Date/Time field to a Double (choose Number as the type and Double as the
field size) and look at what the field is actually storing.

The solution may be as simple as multiplying the field by -1 (to get rid of
the negative) and display the Date/Time field with a time format.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jerry Whittle]

Access stores dates as numbers and not anything that actually looks like
20/01/2010. It formats that number into a "date".

Just a few seconds ago Access says that the time was 40199.4264467593. The
40199 is the number of days. The decimal part is the time. Noon is .5.

Guess what day 0 is? Yep. 30/12/1899. (There's a long reason why it isn't
31/12/1899 or even 1/1/1900, but I digress.)

Therefore the plain 9:00:00 AM is stored as 0.375 which gets you the strange
year.

You can just format the date to show only the time.

Format([FieldName], "Medium Time")

 

[Sylvain Lafontaine]

There is no Date or Time fields in Access, the only thing that exists is the
DateTime field which is both a date plus a time. If you import a time
value, it is converted to a datetime automatically with the addition of the
date 30/12/1899 as the date part. You cannot do anything to stop that or to
remove that if you want to keep a DateTime field (instead for say, to use a
characters field to store the time as a fully formated string).

Many controls in Access will understand that and won't display the date,
only the time, when it is 30/12/1899. However, it's still there.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)