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

N

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.
 
F

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
 
R

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)))
 

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

Top