How do I work with dates before 1900 in Access?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have an Excel 2007 workbook with dates from the 1870s through 2009. When I
import the spreadsheet in Access 2007, everything transfers okay, except the
1800s dates. Is there a simple way to may this work?
 
I have an Excel 2007 workbook with dates from the 1870s through 2009. When I
import the spreadsheet in Access 2007, everything transfers okay, except the
1800s dates. Is there a simple way to may this work?
Click to expand...

Do the dates have four digit format? Access correctly handles dates in the
range 1/1/100 through 12/31/9999 so unless you're working with ancient history
you should be OK.

What is in your spreadsheet, and in what way is it failing to import
correctly?
 
John, thanks, that's what I thought, but no dice. I have several columns of
text. The columns with dates in them were also originally entered as text
(not in an Excel format, but I saved it as an Excel workbook), which I have
manually reformated as Date - m/d/yyyy (all dates are 4 digit). The Excel
file is linked to Access (I couldn't make direct load of the file convert to
Access). All the date columns come across, except when the date is prior to
1900, it shows as #Num!
 
John, thanks, that's what I thought, but no dice. I have several columns of
text. The columns with dates in them were also originally entered as text
(not in an Excel format, but I saved it as an Excel workbook), which I have
manually reformated as Date - m/d/yyyy (all dates are 4 digit). The Excel
file is linked to Access (I couldn't make direct load of the file convert to
Access). All the date columns come across, except when the date is prior to
1900, it shows as #Num!
Click to expand...

Wow. Seems to be an Excel limitation! From the Help it appears that date
serial numbers start at January 1, 1900 and I can't find any provision for
ealier dates. I'd try storing the dates as Text in Excel, and importing or
linking; you can then use the Datevalue() function in Access to convert the
text string "3/11/1875" to a date.
 
John, again thanks. I think you're right. I'll try that, but it sounds as
if I went through a lot of work this morning for nothing.
 
Back
Top