How to Convert 24/03/2010 as text into 03/24/2010 date format

[Nitesh]

Hi ,

I’m using Ms Excel 2003 and in my excel sheet there is a column in which
dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).

Can excel programming is required to convert such date or is there any excel
function.

Please suggest.

 

[FSt1]

hi
you could use text to columns
high light the date.
on the menu bar>data>text to columns
step 1 - select delimited, click next
step 2 - select tab, click next
step 3- upper right "column data format", select date.
from the drop down, select DMY, click finish.

regards
FSt1

 

[Nitesh]

Hi Sir

Thanks a lot.

 

[Ron Rosenfeld]

Hi ,

I’m using Ms Excel 2003 and in my excel sheet there is a column in which
dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).

Can excel programming is required to convert such date or is there any excel
function.

Please suggest.

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

Format the result as a date, if it appears to be some five digit
number.

But be aware that if your entry people are also entering dates where
the day of the month is 12 or earlier AND the cell was not previously
formatted as TEXT, Excel will convert these into "real dates", and the
above method will not work.

If that is the case, you might consider:

=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,4,2),
LEFT(A1,2)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))