Exporting Data to Excel-Date Format Changed

[Nick B]

When exporting large amounts of information into Excel (Office 2000) some of
the date formats are changed but not all.Most are kept as DDMMYY but some are
turned around like US format into MMDDYY.Before exporting it will read
11/4/05 but after exporting into excel becomes 4/11/05.As i say 80% of dates
remain the same as from Citrix program they been exported from.Any ideas
anyone?

 

[Harald Staff]

You may have serious problems, a volatile mixture of correct dates, wrong
dates and text that look like dates but has no date value. So when you say
that "most dates remain the same" -try changing date format for the cell and
see if it even can be done. If not then it's not a date, it's just text.

The idea would be to ensure that this doesn't happen. You don't provide any
detail on the export routine, but the problem is there.

HTH. best wishes Harald

 

[Nick B]

Impossible to say entries were input incorrectly as program within citrix
that they were entered in only allows for correct English data format to be
input.This does not allow it to become a text field. All cloumns are
highlighted within the citrix based program and then export is clicked and
excel chosen to export to and it inputs all columns into excel apart from
this strange issue.

 

[Niek Otten]

Probably all dates with a day<= 12 are changed. If the day is >12, Excel
sees that can't be a month and only then assumes it is a European date. So
maybe 80% of your dates are in the beginning of the month.
Can't you export dots or stars instead of slashes, so that Excel sees it as
text and you figure it out yourself with formulas?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel