Forum Discussion
Excel .csv Conversion - Date-Formatting keeps returning even with prior formatting
Hi all,
I have an issue regarding the formatting of one column in a dataset. To shortly introduce it: We have a question on how many books participants have at home. They can select 'none', '1-10', '11-25', etc. Excel being Excel, it represents those two mentioned as dates. Before converting my .xlxs-file to .csv, I formatted this column to be non-date (I tried Text, Custom (with choice '0'), and some other options, all have the same issue). After this formatting, I exported the .xlxs as a .csv (not UTF-8 .csv) and it represents it just fine ('11-25' stays '11-25' rather than 'nov-25'). However, when working in this file (it's continually updated, normally), the trouble starts. Mind, it's already in a .csv format by now, and the formatting was done prior to the conversion to .csv. Once I save this harmless looking file, the date-format comes back like a zombie rising from the dead and there's seemingly nothing I can do to change it. All changes to the format afterwards result in a weird integer-string of 5 numbers (45962, in various forms depending on the format but with these 5 integers there).
I already had contact with 2 chat-services on the issue, and together with the internet, they suggested that I could create a new 'Custom'-format (for instance, by saying I expect my data to be '11-25'; see https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8E10019E-D5D8-47A1-BA95-DB95123D273E). However, this would be relevant if I only had 11-25 as potential date-conversion-problem, but 1-10 will be, in infinite American wisdom, also revert back to a backwards date-format (jan-10).
Any idea on how to fix this? At the moment, I can only ever convert the file once, and never again because I cannot make change without it switching back to a date-format.
Help is greatly appreciated, as I'm quite close to committing murder on my laptop if it keeps annoying me like this.
12 Replies
Could you perform a global edit and replace the soft hyphen '-' (hex 00AD) by an En Dash '–' (hex 2013) or possibly a non-breaking hyphen (hex 2010). These would be visually similar to the original but Excel would no longer try to convert the strings into date values.
PeterBartholomew1In a global edit, do you mean to reformat the column? Or simply find and replace? Most 'global edit' searches lead me back to reformatting, it seems. I could replace all the different hyphens I guess, although I'm not sure how this would work when entering new data and then storing it again. At the moment, the moment I save the file after the conversion to .csv, it happily destroys the formatting already in place and I'm back at square one. After I did that, I can't get the actual input back, as Excel has already decided to create a date-column and as a result, any formatting automatically produces this string counted from 01-01-1900 I believe.
If there is any point at which you have a text string comprising
2 digits, soft hyphen, 2 digits
and are in an environment with an editor, that is the moment to convert the soft hyphen into another character. If on the other hand, you already have a mix of 15-20, 15-Oct, 5-Oct then you are faced with the task of unscrambling the omelette.
The values you require will be held within the date so a formula
= IF( ISNUMBER(interval), DAY(interval) & UNICHAR(8208) & MONTH(interval), interval)
would be a start to getting a column that you can Copy / Paste values over the top.
ThomasBraasETH When you save something like "1-10" to a csv file it just becomes a string of characters. No information regarding the Excel format (as text) of that string is saved with the csv file. When you then open the file in Excel, you should see a text import wizard where you have to go through the steps presented to you. In step 3, you need to specify the data type as Text. If you skip all of it and just press Finish at the beginning, Excel tries to be smart and it will guess the data type for you. Hence, entries like 1-10 and 11-25 could be dates. If it encounters 13-18 Excel will conclude that this can not be a date and imports it as a text. So, it's important that you do the data typing yourself.
And by the way, the "weird 5 digit number" 45962 is actually the day number in the internal Excel calendar for November 11, 2025. The day counter start at 1 for January 1, 1900.
Riny_van_EekelenI'm not exactly sure if that solves the problem I've been having, or I'm simply not getting your idea. I already have a data-file, an .xlxs file. I converted this successfully to a .csv, still in Excel. I get that Excel is transforming the data automatically because I have not specified each and every column. I did that because I don't care about the 50 other columns in my datafile but only the one column that actually causes this whole headache. As such, I successfully transformed the one column I was interested in and if I don't change anything, that formatting is still there in the .csv file. The moment I add any row in that .csv file, and save the file (again, as .csv, as it already was), it removes the formatting it had before and that one column suddenly appears as date again.
As such, I'm not importing text into excel, the file is an .xlxs file with a column that could be regarded as text, so to speak.
