Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unwanted number to date conversion while pasting data from web | Excel Worksheet Functions | |||
Date conversion | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Disable automatic date conversion feature | Excel Worksheet Functions |