If you follow the directions in my previous post exactly, they should take
care of the problem you describe......

Vaya con Dios,
Chuck, CABGx3



"Sameer" wrote:

CLR wrote:
Assuming your dates are in column A, put this in B1 and copy down

=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,FIND("/",A1,1)+1,2),LEFT(A1,2)),A1)

Now all of the dates in column B should be real dates, so then just reformat
column B as Custom, DD/MM/YYYY

Then you can Copy PasteSpecial on column B and delete column A if you wish.

hth
Vaya con Dios,
Chuck, CABGx3


"Sameer" wrote:

Hello,
I am having some data in my worksheet which I have pasted from a
database.
There is one column containing dates.
Here in India,we follow dd/mm/yyyy date convention.
So when the date is of the format 28/09/2006, i.e. when the day is
greater than 12, the entries in the cell are interpreted as text values
and they are left aligned.
The other dates are treated normally but still Excel treats that the
month being day and day being month.
This is large database so what to do to treat them as dates in Excel in
dd/mm/yyyy.
I am using Windows 2000. Do i need to use Control Panel Regional
Settings?

One solution for me is to extract day, moth and year from text and
forming date using these values using the DATE function.

Please comment.



I was able to create dates from text values by text string manipulation
and DATE function.
But the values which are originally there in date format are like this.
The value is 1/9/2006. The excel is treating it in mm/dd/yyyy format
though the date is expected to be in dd/mm/yyyy format.
How to let it treat in dd/mm/yyyy? Is there any way to take date and
create another date with day and month exchanged?
I am not able to do operations on the dates as they are not properly
treated by Excel.