This formula which has worked in Calc since Open Office was a product of Sun Microsystens and up until last month in LO a couple of weeks ago is no longer working.
=SLOPE($C$1:$C$2,$A$1:$A$2)
The Column C cells are in Numeric format, The A cells are and have always been in Date fprmat.
I believe that the reason is because that Calc is no longer converting dates in cells to their numeric format, which it always done in the past.
Any suggestions on how to correct this bug?
Or the source of your dates changed a bit.
.
Or the type is altered/limited by new clipboard-handlers.
.
If you type the dates: Have you checked the date acceptance patterns? Should not change on its own, but maybe you got a new profile with the new version. (Usually not I know).
.
Or something else…
A lot of people had problems with this in the past. So it may only be the first time you face problems here, not necessarily a bug. Please provide a file to test, and tell where your data comes from.
Please note: Formatting changes, what is on display, not the actual type. So, if you paste Text it stays text (useful to protect leading 0 in Phone numbers 0012345) and is not shown as a date.
For a test change formatting to numeric and back, to see if your dates are all changing on format. (A quick way to convert textual dates is the function “text-in-columns” wich can trigger the recognition of your dates…
PS: “Newest version” is no useful term, as now several people get different new versions by linux-distributions, snap, flatpacks and even on Windows we have win-get and chcocolatey as well as different portable packs.
The number format does not matter at all - as long as it it not the text format which inhibits any evaluation of input.
All dates in spreadsheets are numbers - day numbers. Today is day 45258 in any version of Calc, Excel and similar programs. Any calculation with 45258 will always give the same result, even if it is formatted as $45,258.00.
LibreOffice makes this unnecessary complicated because too many OpenOffice users where reluctant to accept simple rules. You may have to define by your own how dates should be recognized.
Under Tools>Options>LanguageSettings>Languages, there is a “date acceptance pattern”. The built-in acceptance pattern depends on the option above, the locale setting. Accept the default pattern or define your own pattern. It remains active as long as you do not change the locale in that dialog.
With an English locale setting, the default is:
1/ enters this month’s first day number
1/2 or 1/2/ enters this year’s first of February or second of January with US English locale.
1/2/3 enters 1st of Feb in 2003 or second of January with US English locale.
An ISO date like 2023-11-28 is accepted with any locale and with any acceptance pattern.
SOLVED
Problem turned out to be that the latest dates in the spreadsheet were being interpreted as text rather that in date/numeric format. Copied the date sequence values that were correct info in a new spreaadsheet,Created a new Slope function and then replaced the bad ones in the original spreadsheet.
“Newest version of Calc”?
What do you think will be the newest version of Calc for a user having a related or similar problem next year?
Scattered over Europe by the hundreds we have settlements named “New Town” or similar or having a name roughly expressing the same semantics.
Nea Polis (ancient Greek for “New City”) is now called Napoli in Italian, but didn’t readjust its name for about 3000 yerars now.
You might find View > Value Highlighting (Ctrl+F8) helpful to check what is really text or numbers. Value Highlighting
My description left out that It now returns “VALUE”
If I use @Villeroy sample and change the A1 to yesterday’s date and A2 to today’s date then it still works.
If I change the dates (not converting dates to numeric) or the values to text I get a divide by 0 error
If I change $C$1:$C$2 to $D$1:$D$2 (blank cells) I get #VALUE! . Check that your formula is pointing where it should be.
Version: 7.6.3.2 (X86_64) / LibreOffice Community
Build ID: 29d686fea9f6705b262d369fede658f824154cc0
CPU threads: 8; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-NZ (en_NZ); UI: en-GB
Calc: CL threaded
… And if you put '2023-01-01 and '2023-02-01 (note the apostrophes) into A1 and A2, you get the #VALUE!. And you also have that in LO 3.3, AOO 4.1, and OOo 2.2.
Nothing else to say without a sample that actually works in older versions, but doesn’t in newer.
The current version (V1.3) of ODF specifications for Calc formulas, and also the previous one state explicitly (both under 6.18.69) for the SLOPE() function:
"For an empty element or an element of type Text or Boolean in Y the element at the corresponding position of X is ignored, and vice versa."
(X and Y stand for the first and the second parameter position of SLOPE(X; Y) respectively here.)
I would say this explicitly excludes automatic conversion of any passed element from text to number.
It also means that “dates” passed as paramter elements must be numbers just formatted as dates. They may be entered in any format listed under “Date acceptance patterns” then.
You may force such a conversion with the help of the VALUE() function or -a bit sloppy resorting to an automatism- by prefixing an addend 0+ to the expression like in =0+"132" in the formula.
