| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- EH003268 ------------------------------------------------------------------------ EH003268's Profile: http://www.excelforum.com/member.php...o&userid=11806 View this thread: http://www.excelforum.com/showthread...hreadid=525603 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
On Thu, 23 Mar 2006 02:48:49 -0600, 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 =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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
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: http://www.excelforum.com/member.php...o&userid=11806 View this thread: http://www.excelforum.com/showthread...hreadid=525603 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
On Thu, 23 Mar 2006 06:05:22 -0600, EH003268
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date conversion formular
=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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Automatic, Non-Updating Date Stamp | Excel Discussion (Misc queries) | |||
| any formular to deduct an amount automatically on a given date? | Excel Discussion (Misc queries) | |||
| Calculate 1st of month date from existing date. | Excel Discussion (Misc queries) | |||
| Date Conversion | Excel Discussion (Misc queries) | |||
| unwanted number to date conversion while pasting data from web | Excel Worksheet Functions | |||
Linear Mode
