1

I'm newly developing with the office.js and I would like to know if there is a way to convert serial numbers (Date Values) into date formatted using Excel Javascript API.

I have this custom function:

/*
 * @customfunction
 * @param {number} first
 * @param {number} second
 * @param {string} third
 * @returns {number[][]}
 */

  function GETDADOS(first, second, third) {
    var promise = new Promise(function(resolve){
      $.ajax({
        ...
        //HTTP requisition
        ...
        }
      });
    })
    return promise.then(function(result){
      var data = result
      var resp = JSON.parse(data)
      return resp
    })
  })
  };

That provides me this output:

[[44225,1.8541],[44232,1.874],[44239,1.94]]

The first column is the date value to be transformed.

EX: US 35062 -> 12/29/2021, UK 35062 -> 29/12/2021

I saw this way but I'm feeling a little bit confused:

convert date serial number to date using javascript

DeSanterra
  • 81
  • 1
  • 1
  • 8

1 Answers1

1

You may use Excel JavaScript API to call built-in Excel worksheet function TEXT.

await Excel.run(async (context) => {
    var res = context.workbook.functions.text(44225, "mm/dd/yy");
    res.load("value");
    await context.sync();
    console.log(res.value); // "01/29/21"
  });

Note that to call Excel JavaScript APIs from your custom function, you'll need to use a shared JavaScript runtime. See Configure your Office Add-in to use a shared JavaScript runtime for more details.

Guangshuai Liu
  • 11
  • 1