1) if the cell is a date it needs to have the month and date switched

Tried this and it didn't work

2) if the date is text then it needs to be converted into a date

The date is being read by Excel as 'general' data. The contents
of the cel are not altered when a differnent data type is chosen.

Dav, can I send you the document and maybe you will at least have a better
idea of the problem and we save us both time. If you can't solve it at least
you'll be able to better articulate the problem on this board.

Thanks
Andrew

"Dav" wrote:


Excel is expecting the data in the dd/mm/yyyy format and some of the
dates that are mm/dd/yyyy would appear to meet this format so they have
become a date (all be it incorrect), the others it has just recognised
as a text string

If the value is in the cell A1 there are 2 things you need to be able
to do
1) if the cell is a date it needs to have the month and date switched
2) if the date is text then it needs to be converted into a date

As dates are number the following should work although you will need to
format the cell yyyy-mm-dd

in another cell say b1 put
=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)), DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

copy this down by the side of all your dates

then copy all of it and paste special on top of itsself as values to
loose the formulas

copy again and paste on top of the original data, you can now delete
the column you added

If this does not work please get back to me

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142