Apache OpenOffice (AOO) Bugzilla – Issue 14974
Date processing is iffy on imported files using =sumif()
Last modified: 2013-08-07 15:12:51 UTC
I've got some data that is exported from a database and which I import in a spreadsheet. I'm using the sumif() function to do calculations based on date values and for some reason, my file does not recognize dates as being the same. For example, in the attached file, Calc never recognises the dates after April 27, even if the date is cut and pasted from the cell in column A. I'm attaching the faulty document and the imported CSV file.
Hmm, turns out to be a partial user error. On top of that, I had problems with my browser so I posted it three times.
closed
*** Issue 14975 has been marked as a duplicate of this issue. ***
*** Issue 14976 has been marked as a duplicate of this issue. ***
Ok, I worked the data again and I saw the problem once again. I'm attaching the file. I also corrected the version number.
Created attachment 6488 [details] Problematic spreadsheet
Created attachment 6489 [details] Associated data imported
You can see the problem on the April tab. You can also try this: - Import the data and specify that the format of the first column is YMD - Paste the data for April in sheet "Apr 03" - You will notice a bunch of Err.503 - Copy the content of cell A2 to F2. The data in G2 is now correct as well as the data in D2 to D20 - Using the black square in cell F2, drag to F31. - Only the first 5 entries are now correct - Copy the cell from A96 to F7. G7 is now correct - Drag cell F7 to F31 - Now all data is correct If you reimport the data file, you will have to repeat the same steps, which is illogical (to me, anyway).
Hi, this problem is caused by a different date setting. If you give A2 and F2 the default format, you will see that in both cases the numbers get decimal places. The pasted ones are slightly different, so the they do not match and the result of the sumif in column G is zero. Therefore the calculation in column D do an Division by Zero resulting in Error 503. So we have two bugs : 1.) The import of CSV date data should not set any decimal, just the integer except a time is given in the file. 2.) make sure you get the correct data to compare. Will say if date without time is set as format, just use the integer part to compare. Best regards Frank
set the Target and some more flags.
DR->ER: CSV import and number formats -> your issue.
A date value must be imported without a fractional value. It was handled that way in 1.0, so this has to be fixed before the 1.1 release. The SUMIF formula doesn't care about the cell's format and uses the full value. This won't be changed.
started
Fix reviewed
Fixed in CWS calc 13 (sc/source/ui/docshell/impex.cxx 1.24.28.1), will be in 1.1 RC.
Back to QA for verification.
reset to fixed
found fixed on cws Calc13 using solaris and windows
Grrr, forgot to set the verified flag
found integrated using srx645m4s2 on Linux, Solaris and Windows