Date format lost in translation

[crownedzero]

I'm exporting a table from access to an Excel spreadsheet, the date format within the table is YYYYMMDD, however, when exported and opened in Excel it converts the date to MM/DD/YYYY. Normally this wouldn't be a problem except I'm uploading it via FTP and it requires YYYYMMDD, I'd like to make this process entirely automated and would prefer the date remain unchanged. Any ideas?

 

[DCrake]

How are you converting your date to YYYYMMDD? If you do it in a query using Format(YourDate("YYYYMMDD") then this will be treated accordingly.

David

 

[crownedzero]

Atm the moment it is just set in the table field properties as I'm exporting the table.

 

[Brianwarnock]

The date remains unchanged whatever the Format which is merely the visual presentation, check the cell format in Excel, and set a custom Format if you require to see it as YYYYMMDD

Brian

 

[crownedzero]

I guess what I'm asking is if there's a way to force the same format to be carried over so it doesn't change.

 

[Brianwarnock]

I think Excel will recognise the Date and use your Default date format.

Brian

 

[BamaColtsFan]

When you do the export, does it have to be a date format? Why not just export it as text? If you are sending it out via FTP, I'd venture that it doesn't read as a date during transmission, regardless...

 

[SOS]

Yes, use a QUERY for export and format it in the query as text.

 

[crownedzero]

It's done in such a manner, DefaultValue is Date(), Format is YYYYMMDD and the field type is text.

 

[namliam]

If your using excel for a data transferal and you need the date column to be a date... the format doesnt matter... its mearly a format...

Alternative if you need NEED YYYMMDD, then you need the column to be something different than date... you will need a number or string field... not date.

My advice... If/when/where possible avoid Excel as a data transfer vehicle... Use Text files instead.... Much more controle over what goes accross in what format and how....
Excel and other M$ products sometimes try and think for you... which sometimes is a good thing.... sometimes not so good...

 

[SOS]

If you format that date to YYYMMDD in a query to export (instead of trying to export the table) then the formatting in the query will coerce it to a text field and then the export should work. Relying on formatting at the table level is flawed and will fail more times than not, including for reports. So, it is best to do the formatting at the point where you need it, not at the table level.