Why Python String auto convert into Date in microsoft excel

Question

I am writing data into a CSV file. file contains data related to student marks like 6/10, which means 6 out of 10. here the issue is when I open this file with Microsoft excel 6/10 becomes 6-Oct. if anyone have an idea how can we stop to converting a string to date.

need solution from code side not an excel side

Answer 1

That's not really a Python string issue, but an issue with Excel trying to be smarter than it should be.

If you open Excel and write 6/10 into a cell, it will convert it to a date by default.

One solution (both in Excel and from other XLS generating software) is to explicitely set the cell format to TEXT. Not sure how to do this in your current attempt, but e.g. openpyxl offers such an option.

Other solutions include to prefix/wrap your content:

1. use ' (single quote) prefix

   '6/10 will display as 6/10.

2. use (single space) prefix

   6/10 will display as 6/10.

   Same as above, however the space will be part of the displayed cell data, you probably don't want this.

3. Wrap in ="your_content_here"

   ="6/10" will display as 6/10

4. Prefix with 0 (zero and space)

   0 6/10 will display as 3/5 (which mathematically is the same as 6/10)

   Now this one works only for fractions, such as your 6/10. In theory, this should change the cell format to number/fraction, while internally it's the numeric value 0.6.