SV year problem - excel changes "01 / 02" into "01 / Feb" etc

yossarianpickwi

Hi All,

I have written a script that converts a report table to a CSV download but the years, which have to be formatted in '02 / 03' format (ie 2002 / 2003 in shorthand) are getting autoconverted in Excel to "date / Month" .

This is rather frustrating.

Can anyone suggest how I might overcome this? Is there a way to specify to NOT autoparse these fields in excel?

Thanks

David

Installer

I think that if, when entered, the data is enclosed in double quotes that excel will not "auto-type" it as date and instead will accept it as text.

You might get a better answer from an excel forum, such as this one

yossarianpickwi

If I put the dates in quotes, in Excel it looks like this: "01 / 02" with the quotes carried through (ie, it doesn't strip the quotes and treat like text - it treats like text WITH the quotes)

Thanks for the link, I'll crosspost there also (but any wise readers here please post too :-) )

yossarianpickwi

I have found a pretty ugly work around but I post it here because I cannot find any solution to this on the internet and hope this could help someone else.

I hope someone can provide a better solution but if you change the .csv file so that the line:

,01/02, 02/03, 03/04, 04/05

Is changed to:

," "01/02, " "02/03, " "03/04, " "04/05

It will force a space before the numbers and stop Excel formatting the cell. Now, of course, this is a rather invalid way of fixing this but it is better than having my row reformatted entirely out of context.

HTH (and please post better workarounds)

David

yossarianpickwi

A better solution was emailed to me:

Change the dates row to look like this:

="01/02",="02/03",="03/04",="04/05"

The ="" forces Excel into 'text' mode without carrying the " through.

Thanks for your help.