Date Conversion
 

I have a table from an Access database that I am exporting into Excel to be
used in various spreadsheets. The Access table has numerous fields, but the
ones I will be using are account numbers, balances and dates for
transactions. The problem that I have is that the Access table is storing
the date as a number, specifically as a long integer. For example, December
31, 2003 is stored as 2453005. How can I convert this number into a date
once I get it into Excel? I tried just changing the date format from number
to date, but that gives me a date way off in the future, like ten years from
now.

I see what you mean, on my PC it translates to 5/2/8616, therefore about 6611
years in the future.This means you have to subtract 2415019 from the original
date, to format the result to read 31/12/2003. Unless one of the boffins
come up with a real explanation, you can use a helper cell. Say your actual
date is in cell G1, then in cell H1 insert the formula =G1-2415019. Format
this cell as date, and you will get the right answer.

"jdmcleod" wrote:

I have a table from an Access database that I am exporting into Excel to be
used in various spreadsheets. The Access table has numerous fields, but the
ones I will be using are account numbers, balances and dates for
transactions. The problem that I have is that the Access table is storing
the date as a number, specifically as a long integer. For example, December
31, 2003 is stored as 2453005. How can I convert this number into a date
once I get it into Excel? I tried just changing the date format from number
to date, but that gives me a date way off in the future, like ten years from
now.

I have an Access database with a date/time field, and for the date Mar 18,
2005, the number that is stored in the database is 38,429 -- nowhere near the
range of the number you quote. 38,429 is the same number that is stored in
Excel for the same date.

So.... the data in the Access field is being translated to some other units.
Maybe if you enter a transaction with the date Jan 1, 2004 and tell us what
that number is, we can figure out what the units are. If, say, the difference
between the numbers for 12/31/2003 and 1/1/2004 is 86400, it represents number
of seconds; if 1440, it's number of minutes, if 24, it's number of hours.

But, as I said above, that doesn't correspond with what I find in my Access
(2000) database. The dates are stored the same way as in Excel.


On Sat, 19 Mar 2005 10:41:01 -0800, Kassie
wrote:

I see what you mean, on my PC it translates to 5/2/8616, therefore about 6611
years in the future.This means you have to subtract 2415019 from the original
date, to format the result to read 31/12/2003. Unless one of the boffins
come up with a real explanation, you can use a helper cell. Say your actual
date is in cell G1, then in cell H1 insert the formula =G1-2415019. Format
this cell as date, and you will get the right answer.

"jdmcleod" wrote:

I have a table from an Access database that I am exporting into Excel to be
used in various spreadsheets. The Access table has numerous fields, but
the
ones I will be using are account numbers, balances and dates for
transactions. The problem that I have is that the Access table is storing
the date as a number, specifically as a long integer. For example,
December
31, 2003 is stored as 2453005. How can I convert this number into a date
once I get it into Excel? I tried just changing the date format from
number
to date, but that gives me a date way off in the future, like ten years
from
now.