Edit

Share via

Work with dates using the Excel JavaScript API and the Moment-MSDate plug-in

  • Article

This article provides code samples that show how to work with dates using the Excel JavaScript API and the Moment-MSDate plug-in. For the complete list of properties and methods that the Range object supports, see the Excel.Range class.

Note

The Excel JavaScript API doesn't have a "Cell" object or class. Instead, the Excel JavaScript API defines all Excel cells as Range objects. An individual cell in the Excel UI translates to a Range object with one cell in the Excel JavaScript API. A single Range object can also contain multiple contiguous cells. See Work with cells using the Excel JavaScript API to learn more.

Use the Moment-MSDate plug-in to work with dates

The Moment JavaScript library provides a convenient way to use dates and timestamps. The Moment-MSDate plug-in converts the format of moments into one preferable for Excel. This is the same format the NOW function returns.

The following code shows how to set the range at B4 to a moment's timestamp.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");

    let now = Date.now();
    let nowMoment = moment(now);
    let nowMS = nowMoment.toOADate();

    let dateRange = sheet.getRange("B4");
    dateRange.values = [[nowMS]];

    dateRange.numberFormat = [["[$-409]m/d/yy h:mm AM/PM;@"]];

    await context.sync();
});

The following code sample demonstrates a similar technique to get the date back out of the cell and convert it to a Moment or other format.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");

    let dateRange = sheet.getRange("B4");
    dateRange.load("values");

    await context.sync();

    let nowMS = dateRange.values[0][0];

    // Log the date as a moment.
    let nowMoment = moment.fromOADate(nowMS);
    console.log(`get (moment): ${JSON.stringify(nowMoment)}`);

    // Log the date as a UNIX-style timestamp.
    let now = nowMoment.unix();
    console.log(`get (timestamp): ${now}`);
});

Your add-in has to format the ranges to display the dates in a more human-readable form. For example, "[$-409]m/d/yy h:mm AM/PM;@" displays "12/3/18 3:57 PM". For more information about date and time number formats, see "Guidelines for date and time formats" in the Review guidelines for customizing a number format article.

See also