 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Date conversion formular
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 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 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 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. >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 som cells. The solution from Ron worked a teat. Thanks again Clayto >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 > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.