Excel 2002: How to make the date format uniform ?


Mr. Low


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

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:

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



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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question