Can I re-open this topic...

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...).  Exporting to CSV yields the date in the same format... this 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).

As such I'm pretty much unable to perform meaningful exports from my Eclipse Client on this machine where I have to depend on the date columns.

My Regional settings are ignored - the 'Mmm d, yyyy' format is not evident anywhere in my system config.

Connecting to the same project area via Eclipse from a different machine yields output date formats that are recognisable by Excel (generally yyyy-mm-dd formats).

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. The above solution does not work for me... Excel doesn't recognise YYYY-MM-DDtHH:MM:SS:MMMh either due to the 't' in the middle and 'h' at the end).

Thanks

RD

This nasty excel formula might give you what you need ....
It will convert strings like "Oct 13, 2013 11:09 AM" or "Oct 3, 2013 8:05 AM" into an excel time stamp.  It could be modified to support other text date formats that excel does not support out of the box.

In this example the text date value from the CSV file is in cell G2 and the formula converting to a excel date/time value is in cell H2. The formula find the relevant strings by looking relative to known fixed characters. This is to try and take into account the variable length of some of the strings like day in month etc.

The formula is...

=DATEVALUE(MID(G2,FIND(" ",G2)+1,FIND(",",G2)-FIND(" ",G2)-1)&" "&LEFT(G2,3))+TIMEVALUE(MID(G2,FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3))+1,FIND(":",G2)+2-FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3)))&" "&RIGHT(G2,2))

It pulls the date and time out of the text field then converts it into formats that the excel datevalue and timevalue functions can understand

Just reformat the field as the appropriate date/time format to see excel display the date / time correctly
.
To help understand it, it can be broken down a bit ..

Month =  =LEFT(G2,3)

Day = =MID(G2,FIND(" ",G2)+1,FIND(",",G2)-FIND(" ",G2)-1)

Year =  =MID(G2,FIND(",",G2)+2,4)

Time =  =MID(G2,FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3))+1,FIND(":",G2)+2-FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3)))

AM/PM =   =RIGHT(G2,2)

These functions extract the relevant bits of the date/time out of the string in the csv export. Time was the most difficult bit to extract. I wish excel had regular expression support !!. The strings are then concatenated together in the final excel function to allow excel to convert it into a excel date/time stamp.

If anyone has a simpler excel formula please post it !