Office Scripts

This script gets the current date and time and then writes those values to two cells in the active worksheet.

function main(workbook: ExcelScript.Workbook) { 
  let date = new Date(Date.now()); 
  let dateRange = workbook.getActiveWorksheet().getRange("A1"); 
  dateRange.setValue(date.toLocaleDateString()); 
  let timeRange = workbook.getActiveWorksheet().getRange("B1"); 
  timeRange.setValue(date.toLocaleTimeString()); 
} 

This script reads a date from cell "A1" and translates it to a JavaScript Date object.
It uses the date's numeric serial number as input for the JavaScript Date.

function main(workbook: ExcelScript.Workbook) { 
  let dateRange = workbook.getActiveWorksheet().getRange("A1"); 

  let excelDateValue = dateRange.getValue() as number; 
  let javaScriptDate = new Date(Math.round((excelDateValue - 25569) * 86400 * 1000)); 
  console.log(javaScriptDate); 
} 

Count the number of working days in a month

function numberOfWorkingDays(month, year) { 
  let counter = 0; 
  let date = new Date(year, month - 1, 1); 
  const endDate = new Date(date.getFullYear(), date.getMonth() + 1, 0); 

  while (date.getTime() < endDate.getTime()) { 
    const weekDay = date.getDay(); 
    if (weekDay !== 0 && weekDay !== 6) { 
      counter += 1; 
    } 
    date = new Date(date.getFullYear(), date.getMonth(), date.getDate() + 1); 
  } 
  return counter; 
} 

Calculate the number of days between two dates

function numberOfDays(start, end) { 
  const startDate = new Date(start); 
  const endDate = new Date(end); 
  return Math.abs((startDate.getTime() - endDate.getTime()) / (1000 * 60 * 60 * 24)); 
}