Date conversion formular

E

EH003268

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

Stefi

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:
 
E

EH003268

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 th
position of the month from the front to the middle. Then i believe
can change the format to Date
 
S

Stefi

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:
 
R

Ron Rosenfeld

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
Click to expand...


=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
 
E

EH003268

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

Ron Rosenfeld

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
Click to expand...

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
 
R

Robert_Steel

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
Click to expand...

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
 
S

Sloth

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

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