 |
 | 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 / New Users / December 2007Need to re-arrange a field
We entered about 1000 records including a date column (entered as text) but in the wrong order.. it was entered as ..... dd/mm/yyyy .... e.g. 10/06/1886 I really want it to be ... yyyy/mm/dd.. e.g. 1886/06/10 anyone have a formulae to convert it to an adjoining column in correct order? Many thanks Gerry If it was entered as a date and not as text, then all you need to do is highlight the column, then click on Format | Cells | Number tab and choose Custom from the list. Then in the box just type your format string yyyy/mm/dd and click OK. If they are text values then it could be done with a combination of RIGHT, MID and LEFT functions, but let's see if the above option works first. Hope this helps. Pete > We entered about 1000 records including a date column (entered as text) but > in the wrong order.. [quoted text clipped - 9 lines] > > Gerry Thank you Pete but we avoided entering it as text since it is Genealogy data and of course Excel has problems with old/new dates. It is text for sure. Thanks Gerry > If it was entered as a date and not as text, then all you need to do > is highlight the column, then click on Format | Cells | Number tab and [quoted text clipped - 23 lines] >> >> Gerry OOPS that should have read - we avoided entering it as DATE format because of the problems in EXCEL with old/new dates. Gerry > Thank you Pete but we avoided entering it as text since it is Genealogy > data and of course Excel has problems with old/new dates. [quoted text clipped - 30 lines] >>> >>> Gerry Pete Highly unlikely real dates unless OP is using 1904 date system. Gerry......try this formula =RIGHT(A1,4) & MID(A1,3,4) & LEFT(A1,2) Gord Dibben MS Excel MVP >If it was entered as a date and not as text, then all you need to do >is highlight the column, then click on Format | Cells | Number tab and [quoted text clipped - 22 lines] >> >> Gerry Yeah, I've only just spotted the 1886 in the post - must pay more attention !! <bg> To Gerry - Gord's formula assumes you have entered dates with leading zeros for the day and month part, as you indicated earlier. Pete > Pete > [quoted text clipped - 34 lines] > > - Show quoted text - If you haven't entered leading 0's... it's a bit more complicated but still doable =right(A1,4) will always grab the year (assuming your data doesn't go back prior to 1000... If it does, read below =SUBSTITUTE(LEFT(A1,2),"/","") will return the day and strip out the "/"s The tricky part is the month =SUBSTITUTE(IF(MID(A1,2,1)="/",MID(A1,3,2),MID(A1,4,2)),"/","") should work... I would suggest, for ease of use, putting these in three columns and then concatenating (=A2&"/"&A3&"/"&A4) in a fifth column... then you can always go back and paste values to make it all permanent (so that your spreadsheet isn't constantly trying to do these lookups and text slices) alternatively, if you really wanted to have it in one place, this should work =right(A1,4)&"/"&SUBSTITUTE(IF(MID(A1,2,1)="/",MID(A1,3,2),MID(A1,4,2)),"/","")&"/"&SUBSTITUTE(LEFT(A1,2),"/","") should work If your genealogy data is prior to 1000, you can use the substitute in the first section to strip out the "/"s Thank you all for your help. Will sort it out tomorrow. Gerry > We entered about 1000 records including a date column (entered as text) > but in the wrong order.. [quoted text clipped - 9 lines] > > Gerry
|
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.