|
Home > Archive > MS Access data conversion > April 2005 > Exporting to Excel files
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Exporting to Excel files
|
|
| Jonathan Scott via AccessMonster.com 2005-04-06, 8:05 pm |
| I am currently working on an Access97 database application, and when I
export a table to an Excel spreadsheet, I find that my dates turn out as
other than I would expect or want.
For instance, "00/01/01" will sometimes be displayed "00/01/01", sometimes
as "01-Jan-00" or something similar.
What I need is a way to force ALL dates to be exported to spreadsheet
format in the format "YYYY/MM/DD". Is there a way to do this, efficiently?
Or perhaps I will have to recreate my table with the date fields as text
and put the dates in there as I expect them? (a rediculous idea considering
how wasteful and illogical it is)
Any help with learning to export to formats such as Word97 or Excel97, etc.
would be much appreciated.
TIA,
Jonathan Scott
--
Message posted via http://www.accessmonster.com
| |
| Joan Wild 2005-04-06, 8:05 pm |
| Jonathan Scott via AccessMonster.com wrote:
> I am currently working on an Access97 database application, and when I
> export a table to an Excel spreadsheet, I find that my dates turn out
> as other than I would expect or want.
>
> For instance, "00/01/01" will sometimes be displayed "00/01/01",
> sometimes as "01-Jan-00" or something similar.
The display is just that - a format for display purposes. Dates are stored
as numbers
Today's date is 38412 (the number of days since Dec 30, 1899). The decimal
portion of the number indicates the time, as a portion of the day. So right
now it's 38412.53
It doesn't matter how you format it, the underlying value is what is stored
(both in Excel and Access).
So just open your Excel worksheet after the export, select the column and go
to Format, Cells and format it to your liking. You can even choose a custom
format of "yyyy/mm/dd".
--
Joan Wild
Microsoft Access MVP
| |
| Jonathan Scott via AccessMonster.com 2005-04-06, 8:05 pm |
| Thanks Joan! I found that I can control the output if I also control the
format in Access. I couldn't find the formatting rules that I can apply to
dates, etc. in an Access table, so I was a bit confused. I did the typical
"YYYY/MM/DD" and it worked in Access and Excel, following your example.
Thanks again,
Jonathan Scott
--
Message posted via http://www.accessmonster.com
|
|
|
|
|