Custom Date format values in array

Question

I want to format dates in a Table column in Excel 365 Web using a custom format. I can format a cell but not the cell value picked up in a variable or array.

    function main(workbook: ExcelScript.Workbook) {
        let selectedSheet = workbook.getActiveWorksheet();
        // Set number format for range A2 on selectedSheet
        // selectedSheet.getRange("A2").setNumberFormatLocal("yyyy-mmm");
        let v = selectedSheet.getRange("A2").getValue() ;
        console.log(v);
    }

I want the dates to show up as yyyy-mmm. Can anyone show how to custom date format an array of values?

Answer 1

Depending on what you want to do, there are two options.

Format in Script Only

My experience with Javascript and date formatting has never been a favourable one, not like Excel or any of the Microsoft formatting options across it's platforms you typically have access to.

When it comes to working with Javascript dates, all I could find was cobbling the format together by using a string based approach.

You first need to turn the Excel date into a JS date and then do the work from there.

Try this ...

function main(workbook: ExcelScript.Workbook)
{
  let selectedSheet = workbook.getActiveWorksheet();
  let v = selectedSheet.getRange("A2").getValue() as number;
  
  let javaScriptDate = new Date(Math.round((v - 25569) * 86400 * 1000));
  
  const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  let formattedDate = javaScriptDate.getFullYear() + '-' + months[javaScriptDate.getMonth()];

  console.log(formattedDate)
}

Microsoft actually give you a small headstart with this documentation which I have applied to my answer ...

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/excel-samples#dates

It's not ideal but (I believe) it's your best option.

To apply that to entire range/column, you'd need to use a loop which is far from ideal.

Apply Format to Entire Column

It's pretty simple ...

function main(workbook: ExcelScript.Workbook)
{
  let selectedSheet = workbook.getActiveWorksheet();
  selectedSheet.getRange("A:A").setNumberFormat("yyyy-MMM")
}

... you just need to know which column you want to format it on.

Answer 2

If you use getTexts() it will return an array of the formatted text values. I updated the v variable in your example to have the range A2:A3 and changed the method from getValue() to getTexts():

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set number format for range A2 on selectedSheet
  // selectedSheet.getRange("A2").setNumberFormatLocal("yyyy-mmm");
  let v = selectedSheet.getRange("A2:A3").getTexts();
  console.log(v);
}