Excel 2002: How to make the date format uniform ?

[Mr. Low]

Hello,

I imported some data from another businesss system, obviously the date
format was not correctly converted. At certain row of the cells it is
dd/mm/yyyy while at other rows it is mm/dd/yyyy.

If I use Format Cell > Date > dd/mm/yyyy to correct the format, it does nor
work.

I can only do it in a reversing manner by Format Cell > Date > mm/dd/yy, it
it quite puzzling. However I noticed that the entry in the formula bar is
still remain unchange of its orginal format of mm/dd/yyyy while in the cell
it displays as dd/mm/yyyy.

I am not certain if will be sorted as the usual date entry eith date value
appearing at the formula bar.

May I know if there is a formula to change the format correctly into date
value as follows:

Date
mm/dd/yyyy ----> dd/mm/yyyy
mm/dd/yyyy
dd/mm/yyyy ----> dd/mm/yyyy
dd/mm/yyyy


Thanks

Low

 

[Dave Peterson]

If you're importing procedure creates a text file, then you can rename your text
file to something.txt.

Then use file|open within excel to open that file.

You'll be prompted with that text to columns wizard that allows you to specify
the format for each field. You'll be able to choose date and the correct order
(dmy, mdy, ...) for each date field.