I just imported a giant Procite database into Endnote. Every item in it is dated by day, month, and year, and I need to be able to sort the database on that chronological basis. After 2 days of trying to figure out how to do that, I’m beginning to despair. In Procite, the dates were entered in 23 February 1869 format. I realize I have to change that to an all-numerical format, so I changed everything to 23/02/1869 format… but that doesn’t work either (it now sorts by day, then month, then year)… is it really possible that Endnote will only sort these records chronologically once I change all 9,000 records to 1869/02/23 format? It seems unbelievable that Endnote would be so much less functional than the supposedly far more basic Procite program. Somebody please disabuse me of this fear.
If this really is the only way to do it, can anyone tell me how to make that kind of change to the way the dates are entered without doing each one by hand?
I’m hoping this is something very simple and I’m just missing it. Thank you one and all for your help!
EndNote is able to perform alphanumeric sorts (i.e., alphabetical or numerical order). For example, I created six records using your yyyy/mm/dd format and happened to use the “Notes” field to store the info although any field including a customized field will do. The records were then easily sorted in the EndNote library using the yyyy/mm/dd format (see image) which can be performed for either ascending or descending order.
There are various ways you could converting the records’ dates to the yyyy/mm/dd format. One way would be to: 1) parse out the month, year, day info into three separate fields; 2) recode the months (within the month field) to a numeric code (e.g., “February” becomes “02”); then finally 3) aggregating and rearranging the three fields into the one (year/mm/dd).
Thank you for the time you put into replying to my query!
So it looks as though my worst fears are correct: Endnote has limited functionality with regards to date formats, at least compared with Procite (ie it can only process dates in a single, numerical format).
As for working around this flaw - and I apologize for asking for even more help, you’ve been very generous - but how exactly does one convert 02/16/1859 to 1859/02/16? Or rather, how does one do it 9000 times without going one by one? It was easy enough to use the Change Text command to change January to 01 and so forth, but as for rearranging the order from day/month/year to year/month/day - I don’t know how you would do that using Change Text. Is there a different command I should use?
Unfortunately EndNote does not have the present capacity for parsing out and reassembling data such as your dates but you can still rearrange the data using Microsoft Excel, statistical or other programs. The procedure in Excel will allow you to bypass individually changing each cell and could be automated through a macro. I’m a bit rusty on writing macros, though, so will outline the alternate steps instead which are described below.
The following outlines the procedure based on Microsoft Excel 2010 (Windows 7 Pro):
Refer to Image1 for steps 1 to 4:
Import your data into Excel.
Insert 5 new columns to the right of your date (mm/dd/yyyy) field. The first 3 columns will hold the parsed-out month, day, year data; the fourth column will be used to reassemble the data in a yyyy/mm/dd format. The fifth column will hold the final data.)
Select the first two blank columns to the right of the mm/dd/yyyy column then right-click and select “Format Cells”.
Within the Format Cells window, select “Custom”, enter 0#, then click OK. (The “0#” forces Excel to maintain a leading zero for days or months that have a single digit.)
Refer to Image 2 for steps 5 to 8:
Click to select the column holding the mm/dd/yyyy data, then within the “Data” tab click to select “Text to Column” which will activate the Columns Wizard.
In the Columns Wizard, for Step 1 click “Next” to go to Step 2. Within the Step 2 window click “Other” then type a slash (/) - notice the Data Preview displays the separate fields. Click “Next” to go to Step 3.
In the Step 3 window, click “Text” then click on the first column’s header to change it from “General” to “Text”. Repeat this step for the second column.
To prevent overwriting the new data over the original mm/dd/yyy field, change the Destination’s column to that of the first blank column. (In this case, the original field’s column in the example is “A” which is now changed to “B”.) Click Finish to generate the separate columns data.
Refer to Image 3 for steps 9 and 10:
Now that the mm/dd/yyyy data has been disaggregated, place your cursor in the first blank cell of the fourth column. Then type in this formula in the fx box which will reassemble the 3 separate column data in the yyyy/mm/dd format. (Note that D, B, and C will need to changed to correspond to the column letters in your Excel spreadsheet.)
=(D2&"/ “&C2&”/"&B2)
Copy the cell’s formula (CTRL + C) then select the blank range of cells to paste the formula. (Click the next blank cell below E1 then locate the last cell in the column, hold the Shift key and click - which will highlight the range.) Paste the formula (CTRL + V).
To be able to delete the mm dd yyyy fields without affecting the yyyy/mm/dd column, select the entire yyyy/mm/dd column, right-click and select “Copy”, the click the adjacent (5th) column, then select “Paste”, “Paste Special”, “values” then click OK.
Delete the unneeded column, save the file in a tab delimited format then import the file into EndNote.
CrazyGecko: I’m really grateful for the time you put into replying and explaining all this. I will try this solution at some point soon. I’ll report back on whether it worked.
If anyone from Thomson is listening: It really is pretty incredible that Endnote is so much more primitive than lowly Procite in this one regard! It is problematic that such a huge work-around is necessary for such a simple task. IMHO.
Glad to help and good luck. Forgot to add that if you have the date field in its original mmddyyyy form (without the slashes) Excel can “flip” the the data to yyyymmdd form - so you would just need to perform steps 1 to 5, and in Step 2 of the wizard (select the YMD format). Incorporating slashes necessitates additional maneuvering.
I ended up with an unusable tangled mass in Excel, where all kinds information from different EndNote fields winds up all over the place in different columns, with no discernable consistency. It looks like my database after an explosion.
I have written to Thomson customer support. Hopefully they can find a solution. Thank you again for all your help.
Maybe you need to “split” your file into smaller chunks for Excel then aggregate the files once you’ve completed the date changes. A statistical program like SPSS would be better able to handle the size of your db but you’ll need to get up to speed using the program’s syntax commands.
Hope tech support is able to resolve your problem.
As for “If anyone from Thomson is listening…” - yes we are always listening and we understand that there certainly could be benefits to adding a “true date field” into EndNote. Currently the Year and Date fields in EndNote are text fields with no additional properties added to facilitate special date handling.
Adding a special date field would not necessarily be complex. The challenge lies in backward compatibility. If we add a special date field in a future version of EndNote, we would also need to figure out a way to retrofit the millions of legacy copies of EndNote that are already out there. Creating, testing and releasing dozens of different patches is probably not a realistic option.
But we are certainly open to suggestions on ways to better handle and manipulate dates in EndNote.
I realise I am replying to this thread years too late, and that stuff has evolved, etc. But I and many others could live without true date fields in Endnote if the output options were a wee bit more sophisticated. So, for example, if Endnote could sort months entered as text into the correct order in a bilbliography for magazine articles, that would be superb. If it could do the same for seasons, for quarterly periodicals, that would be the icing on the cake.
[As for “If anyone from Thomson is listening…” - yes we are always listening and we understand that there certainly could be benefits to adding a “true date field” into EndNote. Currently the Year and Date fields in EndNote are text fields with no additional properties added to facilitate special date handling.]
I realise I am replying to this thread three decades too late, and that stuff has evolved, etc. But I and many others could live without true date fields in Endnote if the output options were a wee bit more sophisticated. So, for example, if Endnote could sort months entered as text into the correct order in a bilbliography for magazine articles, that would be superb. If it could do the same for seasons, for quarterly periodicals, that would be the icing on the cake.
