is there a function to fetch date values in excel using office script that will preserve the format?

Question

main program objective : skim through a column of dates and identify the row of current date

below code snippet objective: to print the cell value in date format

function main(workbook: ExcelScript.Workbook){
let sheet = workbook.getActiveworksheet("rainbow")  // rainbow is the sheet name
let range = sheet.getRange("A1:A10) // taking only 10 dates for checking
let values=range.getValues();

for(let i=0; i<10; i++){
    console.log(values[i][0]) 

   }

}

The values of the column A are in the format:

when I expected the date to be printed as it is, the printed answers are

45495

45496

45497 etc

Can someone guide me where i am making mistake?

I tried searching in documentation but couldn;t find any function that can help me

Skin · Accepted Answer · 2024-07-26 09:27:56Z

2

Use range.getTexts() not range.getValues(), it will give you the value you see on screen, not the technical value behind the scenes.

answered Jul 26, 2024 at 9:27

Skin

11.7k3 gold badges16 silver badges37 bronze badges

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

Comments

Salt · Accepted Answer · 2024-07-26 08:09:28Z

Your script is using Excel's date serial numbers, so you need to convert these to proper date strings. Excel stores dates as serial numbers by default. Excel represents dates as serial numbers where January 1, 1900, is 1, January 2, 1900, is 2, and so on. For example, 45495 corresponds to July 20, 2024.

If you want to see those dates in a readable format, you need to convert those serial numbers to JavaScript Date objects. This is how you could do that:

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getWorksheet("rainbow"); 
  let range = sheet.getRange("A1:A10");
  let values = range.getValues();

  for (let i = 0; i < values.length; i++) {
    let excelDate = values[i][0];
    let jsDate = new Date((excelDate - 25569) * 86400 * 1000); // Convert Excel date to JavaScript date
    console.log(jsDate.toLocaleDateString()); // Print date in a readable format
  }
}

Collectives™ on Stack Overflow

is there a function to fetch date values in excel using office script that will preserve the format?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related