I have opened a CSV file in LibreOffice Calc 6.0.6 on MacOS 10.13.6 with the default settings in Calc. Column A has dates in the format “Oct 06 2018 07:20:24”. Columns B-D have numbers. If I check the format the dates are considered “Text” and the numbers are considered “Numbers”. I highlighted column B and selected “Insert Columns Left” and created a new blank column B next to the dates in column A which moved the three columns of numbers in columns B-D to the right to C-E. I tried to calculate the DATEVALUE function in new blank cell B2 from the date in cell A2 so I entered “=DATEVALUE(A2)” for the cell containing the text “Oct 06 2018 07:20:24” and the result in the cell was “=DATEVALUE(A2)”. No calculation of the function “DATEVALUE” was performed. I then tried to calculate in cell B3 the sum of numbers in cells (C3,D3 and E3) and again the result was “=SUM(C3:E3)”. If I go to column F and use the function wizard, the functions work. If I go to the inserted column B (cell B5) and use the function wizard for “=DATEVALUE(A5)” the function wizard shows the result as a number but "OK"ing the calculation results in “=DATEVALUE(A5)” and not a numerical value. Same for the “SUM” function. Checking the format for cell B5 I see it is “Text” so I change it to “Number” but the result remains “=DATEVALUE(A5)”. None of the functions will calculate in the created column B.
The inserted cells inherit format from the columns to the left. Since that was Text, the new columns are also Text. The columns formatted as text don’t try to convert anything entered into them as a number, date, or a formula - they treat that literally as a group of string characters.
You might do one of: 1. Select proper date type for first column in the CSV import dialog (right-clicking its header); or 2. Convert the already-imported column to Date, selecting it and using Data→Text to Columns...; or 3. After you inserted the new columns, but before writing anything into them, set their format to Number.
OK… The results.
-
I imported the CSV and set the first column, A from “Standard” column type to “Date (MDY)”. The other columns are numerical so they were left as “Standard” in the import of the CSV. The dates in the first column that are “Oct 06 2018 07:20:24” imported as “10/06/18 07:20 AM”. The numbers imported as numbers. I highlighted column B and insert left or highlight column A and insert right both now result in “Err:502”.
-
I imported the CSV in default with all columns as column type “Standard”. Then I selected column A and formatted it as “Date” as “MMM D, YYYY”. Inserting the column B and entering “=DATEVALUE(A2)” resulted in “###” which was a date after expanding the column width (“Oct 6, 2018”). Changing the new column to “Number” the column showed the DATEVALUE I was looking for. This was an awful lot of work to get this function to work.
-
I think you meant… "After you inserted the new columns, but writing anything into them, set their format to Number. This result was the same failure as the original failure. No function will work.
Format a cell never change their content so if you can see the result after format it’s because the right value was in the cell but with a wrong format.
The dates in the first column that are “Oct 06 2018 07:20:24” imported as “10/06/18 07:20 AM”. The numbers imported as numbers. I highlighted column B and insert left or highlight column A and insert right both now result in “Err:502”
result of what?
I think you meant… "After you inserted the new columns, but writing anything into them
Yes, you are correct.