can I set cell number format programmatically?

Question

I have an excel js add-in with custom functions, some of them should return date and here I have a problem:

I can format and display this date as a string but in this case, I can't use this date as a date in formulas. or
I can cast it '.ToOADate', and use it everywhere as date, but it looks like a number but not date until I set correct number format manually.

so here is the question: can I set a 'short date' number format programmatically as it happens when I use '=DATE' function?

Bruno Marotta · Accepted Answer · 2023-04-20 09:07:46Z

1

You can use the range.numberFormat to set the shortFormat date. But differently from the answer from Raymond Lu, it is an array of array of strings. You cannot just simply set a string, but you need to set the formatting for each individual cell in the range.

If your range has only one cell you could do:

myRange.numberFormat = [["m/d/yyyy"]];

Please check more information in the official Microsoft documentation:

https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#excel-excel-range-numberformat-member

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-set-format#set-number-format

answered Apr 20, 2023 at 9:07

Bruno Marotta

4886 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raymond Lu · Accepted Answer · 2020-06-30 02:31:43Z

-1

Welcome to Office JS!

I think you could use range.numberFormat API to set the format for the range. the short date number format in en-us is "m/d/yyyy"

range.numberFormat = "m/d/yyyy";

Meanwhile, there is a feature request item in Custom Function's backlog. We would suggest that you could submit your request in uservoice and upvote this feature

edited Jun 30, 2020 at 2:31

answered Jun 29, 2020 at 11:35

Raymond Lu

2,2561 gold badge8 silver badges22 bronze badges

9 Comments

Viktor AV Over a year ago

sounds like a plan, but how to do it in case of custom functions? when I have a construction like export async function fName (params) { return apiRequestResult; //here I have a date }

Raymond Lu Over a year ago

Let me ask around and invite Custom Function team to share their view on it.

Viktor AV Over a year ago

i would be happy to any advice or new idea =) one moment - we are expecting that users will call dozens or hundreds of functions per time, so solutions, like calling the context and looking for addresses of each cell and just then setting format, doesn't look fast enough.

Raymond Lu Over a year ago

From my understanding the column usually is the same number format, therefore, you don't have to set each individual cells, but you can set the whole column. As a result, the custom function return value can be shown as a date.

Viktor AV Over a year ago

i agree in most cases it is true, but one more time, imagine: user placed some function like "=myNamespace.myFunc(params)" and make row or column with this function, size of this row, or column I do not know. and what we have: - the function which will be called as many time as many user wanted, - the function works asynchronously so in what moment and how i should try to calculate range and set numberFormat? ps sorry but this really eludes me

|

Collectives™ on Stack Overflow

can I set cell number format programmatically?

2 Answers 2

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related