Apple Numbers is not preserving formulae involving the Excel DURATION function

[omershelef]

Using the following pattern, i created a formula:

{f: 'L2 - DURATION(0,0,8,0,0,0)'}

the export is working, but when opening the excel i see the following formula instead (which doesnt work and causing an error):

L2−BONDDURATION((DATE(1899,12,31)+DURATION(0,IF(0>60,0−1,0))),(DATE(1899,12,31)+DURATION(0,IF(0>60,0−1,0))),8,0,0,0)

[SheetJSDev]

What version of Excel are you checking? I just tried the simpler case of DURATION(0,0,8,0,0,0) and Excel 2016 processes the correct formula:

#!/usr/bin/env node
var XLSX = require('xlsx');
var wb = {
    SheetNames: ["Sheet1"],
    Sheets: {
        Sheet1: {
            "!ref":"A1:A1",
            A1: { t:"n", f: 'DURATION(0,0,8,0,0,0)'},
        }
    }
}
XLSX.writeFile(wb, "out.xlsx");

out.xlsx

(the displayed error in the screenshot stems from argument errors, for example the fifth argument is the frequency and it should be 1 or 2 or 4: https://support.office.com/en-us/article/DURATION-function-B254EA57-EADC-4602-A86A-C8E369334038)

Can you also share the offending file and some sample code?

[SheetJSDev]

Here's the full story: Excel's DURATION function is actually called BONDDURATION in Numbers and Numbers' DURATION function is completely different! There is nothing we can do to change an explicit decision by Apple to deviate from the Excel names :/ As for the value, you need to pass proper arguments.

Numeric Error

Given how the function works, your example has a few errors:

• the settlement date must predate the maturity (second argument > first argument)
• Coupon rate and yield are specified as numbers (0.08 represents 8%)
• Frequency must be 1 (yearly) 2 (semiannually) or 4 (quarterly) -- 0 is invalid

Try following the example from the Excel help for DURATION:

var XLSX = require('xlsx');
var wb = {
    SheetNames: ["Sheet1"],
    Sheets: {
        Sheet1: {
            "!ref":"A1:A1",
            A1: { t:"n", f: 'DURATION(39448,42370,0.08,0.09,2,1)'},
        }
    }
}

out.xlsx

It opens with the value 5.99377496 in Excel. Numbers also calculates the correct value but mangles the formula:

How to generate a formula with DURATION that works in both places

The addition of the strange DURATION function in numbers is rooted in Numbers' failure to honor date numeric codes. Prefer to use the DATE helper or pass YYYY-MM-DD string dates For example:

            A1: { t:"n", f: 'DURATION("2008-01-01","2016-01-01",0.08,0.09,2)'},

Since Numbers understands those strings as dates, it doesn't need to perform some jujutsu to convert the numeric date argument to string.

Numbers documentation