software

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: www.excelforum.com/member.php...oamp;userid=11806
View this thread: www.excelforum.com/showthread...hreadid=525603USA: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: quot;dd/mm/yyyyquot;Regards,
Stefi

?H003268??ezt ?rta:

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


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: www.excelforum.com/member.php...oamp;userid=11806
View this thread: www.excelforum.com/showthread...hreadid=525603Not nice, but works:

=RIGHT(quot;0quot;amp;MID(F2,SEARCH(quot;/quot;,F2) 1,SEARCH(quot;/quot;,F2,SEARCH(quot;/quot;,F2) 1)-SEARCH(quot;/quot;,F2)),SEARCH(quot;/quot;,F2) 1)amp;RIGHT(quot;0quot;amp;LEFT(F2,SEARCH(quot;/quot;,F2)),3)amp;MID(F2,SEARCH(quot;/quot;,F2,SEARCH(quot;/quot;,F2) 1) 1,4)Regards,
Stefi

?H003268??ezt ?rta:

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

On Thu, 23 Mar 2006 02:48:49 -0600, EH003268
gt; wrote:

gt;
gt;How can i change text 2/27/2002 12:00AM (cell F2) into this date
gt;27/02/2002 ? (cellF3). Also 11/27/2002 12:00AM into 27/11/2002
gt;Thanks=DATE(MID(F2,FIND(quot;/quot;,F2,4) 1,4),LEFT(
F2,FIND(quot;/quot;,F2)-1),MID(F2,FIND(quot;/quot;,F2) 1,
FIND(quot;/quot;,F2,4)-1-FIND(quot;/quot;,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--
EH003268
------------------------------------------------------------------------
EH003268's Profile: www.excelforum.com/member.php...oamp;userid=11806
View this thread: www.excelforum.com/showthread...hreadid=525603On Thu, 23 Mar 2006 06:05:22 -0600, EH003268
gt; wrote:

gt;
gt;Thanks for the replies
gt;The solution from Stefi worked but it put some 0's in front of some
gt;cells.
gt;The solution from Ron worked a teat.
gt;Thanks again
gt;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

gt;How can i change text 2/27/2002 12:00AM (cell F2) into this date
gt;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(quot; quot;,F2)-1))

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

quot;EH003268quot; wrote:

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