1

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?

sifar
  • 1,086
  • 1
  • 17
  • 43

2 Answers2

2

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.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • If it's an column of dates that i need to format and apply back, how do I do it? – sifar Jan 31 '22 at 17:40
  • Have updated my answer, hopefully that helps. – Skin Jan 31 '22 at 22:08
  • in google apps script, i was using something like this to update and format a table date column in one go. `ar.push([b,a,d,e,f,g,,,,,, y,,x,,,,c,Utilities.formatDate(new Date(k),ss.getSpreadsheetTimeZone(),"yyyy-MMM"),m]);` where k is the date or number. I need something like that in a 2d array that i can transfer in one go into the sheet. – sifar Feb 01 '22 at 11:44
  • I’m confused, have you tried my solution? You can format an entire column using my answer. – Skin Feb 01 '22 at 11:50
  • Is the data already in your sheet and you want to format it or do you want to generate new data and load that in? Your question asks the former, not the latter and that is the answer I have given you. – Skin Feb 01 '22 at 12:08
  • no i am picking up the dates (or date numbers) from another sheet an adding them into table column. I know i can do it with .getvalues() and then dump the numbers, then do a column format...but i wanted to know if there was a way to do it like google appscripts Utilities.formatDate which is easily able to setup the format within an array. – sifar Feb 01 '22 at 12:18
  • 1
    No, I don’t believe you can. Just dump the data and then format the column, it’s what you should do anyway because Excel will prefer the raw date format to work with in formulas, can’t speak for Google Sheets in that regard. – Skin Feb 01 '22 at 12:25
1

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);
}
Brian Gonzalez
  • 1,178
  • 1
  • 3
  • 15
  • thanks for responding. The column dates are in date format. I want to apply a Custom format `yyyy-mm` to the date columns. I don't want to return the same dates format. I am not sure it is possible to format array values as per the Office scripts typescript documentation. I think only ranges can be formatted. – sifar Jan 28 '22 at 17:42