CODEWITHSUNDEEP
excel
PJLG
PJLG

Reputation: 105

When saving excel file, numbers are converted to date

I have a file (only one) with some columns with integer numbers. When I change and save file, those columns are automaticaly converted in dates. Does anyone knows how can i prevent this? Thank You!

Upvotes: 3

Views: 4518

Answers (3)

Vadorequest
Vadorequest

Reputation: 17979

I just ran into this issue. I am using a webhook to store data to excel on the fly, adding new rows, updating existing data, etc.

I had a special field "step" which is a string who can be "1", "1.1", "1.2", etc. which was treated as date as soon as the value was a "string decimal"...

The only way I could find to fix this issue was to programmatically add a ' before the value, like '1.2.

I tried to apply this to all my fields to avoid excel side effect, but I then noticed that when updating an existing row by merging new values with existing values then the ' was stripped. So, I end up using a whitelist of fields to escape. I couldn't locate the fields to force adding ' when merging data due to limitations from the lib I use google-sheets-node-api.

Upvotes: 0

Lebowski
Lebowski

Reputation: 121

I guess there is still no solutions to this problem.

It happens in both my computers when inserting data from sql server 2008.

Half of columns becomes dates..... and im supposed to give this to my accountant.. sucks

(Btw i know about the special paste to text, then convert to number... but it takes so much time)....

Upvotes: 0

gaurav5430
gaurav5430

Reputation: 13882

you might want to check out a similar issue here:

http://www.mrexcel.com/forum/excel-questions/637277-excel-converting-numbers-dates.html

(the below suggestions might not be particularly relevant to this issue, but in general, they might help to resolve such a problem)

Selct the cells and go to Format --> cells --> number and select Text for the selection

There's also a one-keystroke solution: type an apostrophe before entering or pasting a pair of numbers that Excel could mistake for a date and month. When you exit the cell, the apostrophe vanishes and the numbers stay numbers, formatted as text.

this is what microsoft says: http://office.microsoft.com/en-001/excel-help/stop-automatically-changing-numbers-to-dates-HA102809473.aspx

also if you are pasting data from somewhere else

try Paste Special

Upvotes: 2

Related Questions