Formatting the cell will change only how the date is displayed, but not what
the value is of the date stored.

The decision on interpretation of ambiguous dates such as 12/8/2009 when you
input them is governed not by the cell formatting in Excel but by Windows
regional Options (in Control Panel). Use this to get the interpretation
right before you put your data into Excel.
--
David Biddulph

"Dream" wrote in message
...
Hi all,

I appreciate your help on the following:

I have a list of dates that i recieved as excel file. the dates are
formatted as month/day/year but the truth of the matter is that it is
day/month/year...for example,

one data point is shown as 12/8/2009, when i click to see its format it is
Date format *3/14/2001 so excel thinks it is december 8 2009...now i want
excel to know that the date is 12/8/2009 which is 12 august 2009...i tried
text to column but the problem is that excel still thinks that the first
number is the month but i want it to know that it is the day not the
month....if i try to change the format to dd/mm/yyyy from custom date,
still
excel is confused so it switched both figures but it is wrong to start
with!!!
i tried to copy past the value and try to text to column with / being the
separator, but then excel will give me the serial number of the date!!

i very much appreciate your help and thanks in advance