Hi everyone!
I am importing SQL data from my ERP to LO Calc (on a M1 Macbook Air), then trying to calculate a number of months between two columns. I have put each of the column in the “DATE” format, have gone to cell Format and moved all the cells to the YYYY/MM/DD format, but I keep getting errors when I type my formula
=DATEDIF(I1,AC1,"m")
Does anyone know what I’m doing wrong?
The explanation for that error code is:
The formula yields a value that does not corresponds to the definition, or a cell that is referenced in the formula contains a text string instead of a number.
I would first check that the “dates” you are using are in fact dates, and not strings looking like dates. View | Value Highlighting will show text cells formatted in black, formulae in green, number cells in blue, and protected cells are shown with light grey background, no matter how their display is formatted.
It is possible that when importing your data, you may not have checked Detect special numbers in the import filter.
Also note: You can manually change the type for the columns in the import filter. Often needed for dates, if the imported dates don’t fit the locale setting of the importing computer.
Let me guess: you imported data from text files (csv) and you ignored most of the import options.
Thanks for the explanation. The two columns are indeed displayed in blue as numbers. But I have selected them and changed into “date”.
The thing is that I imported my data from the SQL of my ERP directly, and I wasn’t aware that it’s possible or mandatory to format data first.
So to summarize: if the data hasn’t imported in the correct format, it can’t be changed later right?
No, my ERP is connected via SQL to the spreadsheet, I haven’t found any otherway to initiate the connection so far than to drag and drop from the data sources, before it can be updated automatically every time. (pretty neat feature by the way!)
EDIT:
CAST(REPLACE("D", '/', '-')AS DATE) AS "Date" should do the job.
Other date converisions:
Add something like this to your query:
CAST(YEAR("D") || '-' || MONTH("D") || '-' || DAY("D") AS DATE) AS "Date"
or use SQL DATEDIFF and calculate the difference in SQL.
or if your dates are strings actually, e.g. MM/DD/YYYY:
CAST(SUBSTR("D",7,4) || '-' || SUBSTR("D",1,2) || '-' || SUBSTR("D",4,2) AS DATE) AS "Date"
If the values are shown in blue they are numbers. However, DATEDIF() will even automatically convert numerical text to numbers, and anyway the result of DATEDIF() does not depend on the number format used in the cells.
A possible reason for Err:502 would be startdate>enddate, but a #VALUE is definitely very strange in this case. Without an example file there may not be any progress.
I hope the sheet can be “detached” from the database and still show the error. Otherwise the problem is due to the connection, and somebody wanting to help will need to have the same configuration
Hi everyone, and thank you for your help. I have reformatted the data in the “data Sources view” and made sure it is in a similar date format once imported, and now it works.
Just FYI I have checked that the original format in the SQL database is a time format, however it doesn’t get carried when pasting to the Calc sheet, I’m curious to know if there’s a way to parameter this before doing the drag and drop ? I haven’t found anything so far.
Many thanks for your help again 