Date conversion formular

How can i change text 2/27/2002 12:00AM (cell F2) into this date 27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002
Thanks

USA:2/27/2002 UK:27/02/2002
Therefore you have to change the format in Windows/Control Panel/Regional
Settings from USA format to UK format.

To omit 12:00AM choose an approprite date format: "dd/mm/yyyy"


Regards,
Stefi

„EH003268” ezt �*rta:

>
> How can i change text 2/27/2002 12:00AM (cell F2) into this date
> 27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002
> Thanks
>
>
> --
> EH003268
> ------------------------------------------------------------------------
> EH003268's Profile: http://www.excelforum.com/member.php...o&userid=11806
> View this thread: http://www.excelforum.com/showthread...hreadid=525603
>
>

Thanks for the reply, but i cannot change the format from text to date.
I am looking for a formuler that will delete the time, move the position of the month from the front to the middle. Then i believe i can change the format to Date.

Not nice, but works:

=RIGHT("0"&MID(F2,SEARCH("/",F2)+1,SEARCH("/",F2,SEARCH("/",F2)+1)-SEARCH("/",F2)),SEARCH("/",F2)+1)&RIGHT("0"&LEFT(F2,SEARCH("/",F2)),3)&MID(F2,SEARCH("/",F2,SEARCH("/",F2)+1)+1,4)


Regards,
Stefi

„EH003268” ezt �*rta:

>
> Thanks for the reply, but i cannot change the format from text to date.
> I am looking for a formuler that will delete the time, move the
> position of the month from the front to the middle. Then i believe i
> can change the format to Date.
>
>
> --
> EH003268
> ------------------------------------------------------------------------
> EH003268's Profile: http://www.excelforum.com/member.php...o&userid=11806
> View this thread: http://www.excelforum.com/showthread...hreadid=525603
>
>

On Thu, 23 Mar 2006 02:48:49 -0600, EH003268
<EH003268.2546xa_1143103801.3918@excelforum-nospam.com> wrote:

>
>How can i change text 2/27/2002 12:00AM (cell F2) into this date
>27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002
>Thanks


=DATE(MID(F2,FIND("/",F2,4)+1,4),LEFT(
F2,FIND("/",F2)-1),MID(F2,FIND("/",F2)+1,
FIND("/",F2,4)-1-FIND("/",F2)))

will convert the text to an Excel date serial number. You can then format it
however you wish. (e.g. Format/Cells/Number Custom Type: dd/mm/yyyy)


--ron

Thanks for the replies
The solution from Stefi worked but it put some 0's in front of some cells.
The solution from Ron worked a teat.
Thanks again
Clayton

On Thu, 23 Mar 2006 06:05:22 -0600, EH003268
<EH003268.254g6q_1143115806.6577@excelforum-nospam.com> wrote:

>
>Thanks for the replies
>The solution from Stefi worked but it put some 0's in front of some
>cells.
>The solution from Ron worked a teat.
>Thanks again
>Clayton

You're welcome.

Thanks for the feedback.

I don't believe, as Stefi wrote, that there is any need to change the
Windows/Control Panel/Regional settings to do what you wanted, either.
--ron

>How can i change text 2/27/2002 12:00AM (cell F2) into this date
>27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002

You have been given options to use formula that will work.
A manual alternative that I have used is the Text to Columns command

Work on a backup to get used to the method.

Select the single column range with the text you wish to change.
Data\Text to Columns
Delimited, Next
Delimited by Space, Next
For Column 1
Column Data Format MDY
For Column 2
Skip
You can select a different destination, The default is to overwrite.
Finish.

You are now, hopefully, left with true Excel dates in whatever your
default format is.

hth RES

=DATEVALUE(LEFT(F2,FIND(" ",F2)-1))

set the format to custom and insert this...
d/m/yyyy

"EH003268" wrote:

>
> How can i change text 2/27/2002 12:00AM (cell F2) into this date
> 27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002
> Thanks
>
>
> --
> EH003268
> ------------------------------------------------------------------------
> EH003268's Profile: http://www.excelforum.com/member.php...o&userid=11806
> View this thread: http://www.excelforum.com/showthread...hreadid=525603
>
>