Bug 97334 (Function-iWorksNumbers) - [Numbers] Add iWorks' Numbers missing functions
Summary: [Numbers] Add iWorks' Numbers missing functions
Status: NEW
Alias: Function-iWorksNumbers
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: needsAppleNumbers
Keywords:
Depends on: 97706 97977
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2016-01-23 16:20 UTC by Dennis Roczek
Modified: 2021-09-27 16:04 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Roczek 2016-01-23 16:20:09 UTC
I have analyzed all Apple's iWorks Numbers functions finally. I will also post my list to the wiki later this weekend.

We do not support all functions of Numbers. The help is accessible at https://manuals.info.apple.com/en_US/Formulas_and_Functions_User_Guide.pdf

Basically all functions do explain themself, some remarks in brackets:
BASETONUM (similar to hex2bin etc. just with another base)
DAYNAME (DAYNAME(1) = Sunday)
DUR2DAYS
DUR2HOURS
DUR2MILLISECONDS
DUR2MINUTES
DUR2SECONDS
DUR2WEEKS
MONTHNAME (MONTHNAME(11) = November)
NUMTOBASE (see BASETONUM)
STRIPDURATION (similar to DUR2DAYS, seems to be included for compatibility with other spreadsheet software, dunno which one it is or where the difference to DUR2DAYS is)

I think we should support these functions for compatibility reasons (and for importing .number pages in the DLP project)
Comment 2 Dennis Roczek 2016-01-24 15:18:25 UTC
A further analysis revealed that our CONVERT_ADD is very similar to Numbers' CONVERT, but that Apple also allows to convert to weeks.
Comment 3 Dennis Roczek 2016-01-25 18:25:20 UTC
Ah, I found the STRIPDURATION difference in Apple's help forums.
https://discussions.apple.com/message/28878054#28878054


Examples
=STRIPDURATION(“1w”) returns 7, the equivalent of one week in days.
=STRIPDURATION(12) returns 12, because 12 is not a duration value.
=STRIPDURATION(“abc”) returns “abc”, because “abc” is not a duration value.


and
This function may be automatically inserted when a Numbers ’08 document is upgraded, or an Excel or AppleWorks document is imported. It is removed in any copy of the file saved as a Numbers ’08 or Excel document.
Comment 4 Eike Rathke 2016-02-05 14:12:48 UTC
@raal:
Why would this be Document Liberation Project / General instead of LibreOffice / Calc? The functions would have to be implemented in Calc. Unless someone came up with a really nifty idea ;-)
Comment 5 Eike Rathke 2016-02-05 14:15:27 UTC
Well, the Numbers filter part could be DLP, true.
Comment 6 Dennis Roczek 2016-02-05 19:52:26 UTC
well I thought the same original. There are two parts: 

* (maybe) libetonyek import filter modifications (at least for stripduration)
* integrate the other missing functions

So I switch that to libetonyek (the correct part) first and afterwards we can change it back to Calc's formula engine.
Comment 7 Dennis Roczek 2016-02-12 09:57:53 UTC
Another function which is missing: DURATION

Apple's DURATION is giving actually a duration (e.g. 1h).

From Apple's help file:
DURATION
The DURATION function combines separate values for weeks, days, hours, minutes, 
seconds, and milliseconds and returns a duration value.
DURATION(weeks, days, hours, minutes, seconds, milliseconds)

weeks: A value representing the number of weeks. weeks is a number value.
days: An optional value representing the number of days. days is a number value.
hours: An optional value representing the number of hours. hours is a number value.
minutes: An optional value representing the number of minutes. minutes is a 
number value.
seconds: An optional value representing the number of seconds. seconds is a 
number value.
milliseconds: An optional value representing the number of milliseconds. 
milliseconds is a number value.
Usage Notes
 An argument that is 0 can be omitted, but the comma must be included if later   Â
values are included. For example, =DURATION(, ,12, 3) would return a duration value 
of 12h 3m (12 hours and 3 minutes).
Negative values are permitted. For example, =DURATION(0, 2, -24) would return a   Â
duration of 1 day (2 days minus 24 hours).

Our DURATION (and MDURATION and DURATION_ADD) are calculation a rates (financial stuff).
Comment 8 Dennis Roczek 2016-02-15 23:16:00 UTC
Excel and Numbers both calculate =MODE(6; 6; 4; 2; 2) as "6". ODF defines 2 as a correct value. I guess we additionally need something link MODE.XCL or MODE_ADD for compatibility reasons, too. :-(
Comment 9 Dennis Roczek 2016-10-23 17:29:36 UTC
BIN2DEC
BIN2HEX
BIN2OCT
DEC2BIN
DEC2HEX
DEC2OCT
HEX2BIN
HEX2DEC
HEX2OCT
OCT2BIN
OCT2DEC
OCT2HEX
have a second optional parameter to add padding 0s

From the Apple help: "convert-length:  An optional value specifying minimum length of the number returned. convert-length is a number value and must be in the range 1 to 32. If omitted, it is assumed to be 1. If included, convert-string is padded with leading zeros, if necessary, so that it is at least the length specified by convert-length."
Comment 10 osnola 2021-09-16 14:14:11 UTC
(In reply to Dennis Roczek from comment #6)
> well I thought the same original. There are two parts: 
> 
> * (maybe) libetonyek import filter modifications (at least for stripduration)

Hi, I'm not sure what you mean here. For example, if in a Numbers' file, we find the following formula:
=stripduration(C2)
what do you propose that the libetonyek import filter does?

> * integrate the other missing functions
> 
> So I switch that to libetonyek (the correct part) first and afterwards we
> can change it back to Calc's formula engine.
Comment 11 Dennis Roczek 2021-09-16 14:20:44 UTC
(In reply to osnola from comment #10)
> (In reply to Dennis Roczek from comment #6)
> > well I thought the same original. There are two parts: 
> > 
> > * (maybe) libetonyek import filter modifications (at least for stripduration)
> 
> Hi, I'm not sure what you mean here. For example, if in a Numbers' file, we
> find the following formula:
> =stripduration(C2)
> what do you propose that the libetonyek import filter does?

from my (unfinished work) in the wiki:
https://wiki.documentfoundation.org/Functions_comparison_of_LibreOffice_Calc_and_Numbers#cite_ref-STRIPDURATION_20-0

STRIPDURATION is included for compatibility reasons with "other spreadsheet software". If AppleWorks, Excel, or Number '08 files are imported to Numbers '09, Apple includes this command where it is needed and removes it when exporting to Excel.

So basically Numbers is working a bit differently and in their own xls(x) export they do modify that part and strip that command.

From Apple's online help:
https://help.apple.com/functions/mac/7.0/?lang=en#/ffa5620514
This function may be automatically inserted when a Numbers ’08 document is upgraded, or an Excel or AppleWorks document is imported. It is removed in any copy of the file saved as a Numbers ’08 or Excel document.

Examples

=STRIPDURATION(“1w”) returns 7, the equivalent of one week in days.

=STRIPDURATION(12) returns 12, because 12 is not a duration value.

=STRIPDURATION(“abc”) returns “abc”, because “abc” is not a duration value.

Does that help? (Man, I'm so lucky that I made that wiki page so well documented!)
Comment 12 osnola 2021-09-16 14:45:45 UTC
(In reply to Dennis Roczek from comment #11)

> from my (unfinished work) in the wiki:
> https://wiki.documentfoundation.org/
> Functions_comparison_of_LibreOffice_Calc_and_Numbers#cite_ref-
> STRIPDURATION_20-0
> 
> STRIPDURATION is included for compatibility reasons with "other spreadsheet
> software". If AppleWorks, Excel, or Number '08 files are imported to Numbers
> '09, Apple includes this command where it is needed and removes it when
> exporting to Excel.
> 
> So basically Numbers is working a bit differently and in their own xls(x)
> export they do modify that part and strip that command.
> 
> From Apple's online help:
> https://help.apple.com/functions/mac/7.0/?lang=en#/ffa5620514
> This function may be automatically inserted when a Numbers ’08 document is
> upgraded, or an Excel or AppleWorks document is imported. It is removed in
> any copy of the file saved as a Numbers ’08 or Excel document.
> 
> Examples
> 
> =STRIPDURATION(“1w”) returns 7, the equivalent of one week in days.
> 
> =STRIPDURATION(12) returns 12, because 12 is not a duration value.
> 
> =STRIPDURATION(“abc”) returns “abc”, because “abc” is not a duration value.
> 
> Does that help? (Man, I'm so lucky that I made that wiki page so well
> documented!)

Not really, ie. I think that you will never find =STRIPDURATION("1w") in a user file as this function is mainly there to be used with a cell reference.

So even if it is possible in the filter to replace STRIPDURATION("1w") by 7, it means to make the filter very heavy (to handle cases that will almost never appear) and I don't think it is really worth it.
Comment 13 Dennis Roczek 2021-09-27 16:04:17 UTC
(In reply to osnola from comment #12)
> Not really, ie. I think that you will never find =STRIPDURATION("1w") in a
> user file as this function is mainly there to be used with a cell reference.
> 
> So even if it is possible in the filter to replace STRIPDURATION("1w") by 7,
> it means to make the filter very heavy (to handle cases that will almost
> never appear) and I don't think it is really worth it.

well then move it to Calc and get the other functions added as Eike was proposing ages ago ;-)