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)

https://wiki.documentfoundation.org/Functions_comparison_of_LibreOffice_Calc_and_Numbers

A further analysis revealed that our CONVERT_ADD is very similar to Numbers' CONVERT, but that Apple also allows to convert to weeks.

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.

@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 ;-)

Well, the Numbers filter part could be DLP, true.

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.

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).

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. :-(

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."

(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.

(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!)

(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.

(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 ;-)