Excel - Convert dates stored as text

Asked By Emece on 21-May-10 03:59 PM
I have Excel 2007 in English, but I sometimes receive data that comes from
another applicationes, so dates are stored as text because they come in
following format: dd/mm/yyyy. And I have the format mm/dd/yyyy.

So, in the same column, I have dates stored as dates, and dates stored as
text. Which is the easiest way to convert them all to date format?

Thanks in advance.


Dave Peterson replied to Emece on 21-May-10 04:53 PM
Be careful.

I'd bet that those values that come in as real dates are not what the original
data represent.

For instance, if you have two values:

The first will come in as Text since your windows short date format (under
Regional settings in the windows control panel) is in mdy order.

But the second value can come in as a date.  The bad news is that you will see it
as Jan 2, 2010.  But the original data is really dmy order, so it should be Feb
1, 2010.

I would not just convert the text (non-dates) to dates.

I'd use fresh data from the original source.

And you will have at least couple of options to make sure you get the correct

The first is to change your windows short date format setting from mdy order to
dmy order.  You can make this change right before you import the data, do the
import and change the setting back.

Or you could bring all the data in as text and then parse it the way you like.

If you are doing File|open and opening a CSV file, you could rename the .csv file
to .txt and you will be prompted with a wizard to parse the records.

Make sure you choose date for the field(s) you need and make sure you match the
same order as the data.  You'll be able to reformat the columns to display the
dates the way you like.


Dave Peterson
bala_vb replied to Emece on 22-May-10 01:37 AM
Emece;955365 Wrote:

I would recommed to get the data which fetches from other application
to seperate column and format to mm/dd/yyyy format, then combine. I
think now you will have all the dates in same format

all the best