Exporting from Adobe produces wrong date format

Topic

New Reply

I have exported some pages from Adobe and the dates when imported look correct as mm/dd/yyyy. However, if I format them as a number or a date format some, but not all, format as dd/mm/yyyy. Any ideas as to why this might be happening?
Thanks.

Reply | Quote

Replies

I changed the format of the date cells to d mmm yyyy – see v2 attached.

They look OK to me – what am I missing ?

Reply | Quote

The locale for the formatting of the dates is set to Chinese. Could this play a part with the behavior you are experiencing?

Reply | Quote

Hi

If you right-click one of the date cells (in the first posted sample file) to check its format, you will see in the left panel the category is Date, and in the Type panel (on the right) that its format is type *14/03/2001
As per the note displayed at the bottom of the tab (under the Category panel), “Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in the operating system”.

So something like 05/07/2009 will be treated as 5-Jul-2009 in UK-type systems but as May 7, 2009 in USA-type systems.
If you have something like 17/2/2010, you can sometimes get errors with USA-type formats because there isn’t a ‘month number 17″.
This is particularly seen in csv exports with dates.
Maybe this is what you are seeing.

And note that Excel VBA ALWAYS assumes USA-format for dates like 05/07/2010 (unless specifically dealt with otherwise) irrespective of whether you are in UK or anywhere else.

zeddy

Reply | Quote

Thanks to all. Since the dataset is reasonable small, my workaround is to visually check the ones that don’t have the correct date and then search and replace the excel date number; e.g., 40365.

Reply | Quote