How to change Date format in Workitem Query Output? (Currently incompatible with Excel)
I have recently changed to a Win 7 machine running Eclipse Client v3.0.1.1. My work item queries now produce dates in format 'Mmm d, yyyy' (e.g. Oct 11, 2012...). Now when exporting to CSV yields the date in the same format which is not recognised as a date by Excel and thus interpreted as a string... highly annoying. I've tried fiddling with Locales via the eclipse.ini file to no avail. (Changing language from en_US to en_UK worked to solve this problem in the Browser client... but setting language to en and region to UK (or US) in eclipse.ini makes no difference). My Regional settings are ignored - the 'Mmm d, yyyy' format is not evident anywhere in my system config. On another note, I see dates in American m/d/yyyy format in my Process Configuration history and in my Synch Status views... although I have UK locale set for my O/S and in eclipse.ini. I connect to the same project area via Eclipse from a different machine and this yields output date formats that are recognisable by Excel (yyyy-mm-dd format). Can anyone advise how I can configure the Eclipse Client to determine the format that dates display on my Work Items and in my exports? (P.S. A previous recommendation I've seen is to show underlying value instead of label for the date fields. This still doesn't work for me as the resultant java "YYYY-MM-DDtHH:MM:SS:MMMh" format is still not recognised as a date by Excel. )
Thanks RD |
Accepted answer
|
Luckily I can answer my own question... As mentioned above, you need to edit the Eclipse.ini file in the same folder as your Eclipse.exe. You need to add the following 2 lines under the '-vm' flag: -Duser.language=en
-Duser.region=GB
(My mistake was I had set -Duser.region=UK. This didn't work - you need GB. When done the work item dates now output in d MMM yyyy format to the screen and export to CSV as yyyy-mm-dd which is recognised by Excel as a valid date. Happy Days.)
Ralph Schoon selected this answer as the correct answer
|
One other answer
Hi,
Another way to do it, is to use Excel formulas to convert the date from text format (e.g. Jan 1, 2015 9:00AM) to an excel format which can be used e.g. for pivot tables. After some trial and error I found this formula (where C2 is the field containing the date in text format): =DATEVALUE(MID(SUBSTITUTE(C2,",",""),5,2)&"/"&LEFT(SUBSTITUTE(C2,",",""),3)&"/"&MID(SUBSTITUTE(C2,",",""),7,5))Make sure the destination *field* is in Date format (otherwise you will see a number instead of the date). Hope this helps... |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.