ExcelScript.DatetimeFormatInfo interface

Package:

ExcelScript

Defines the culturally appropriate format of displaying numbers. This is based on current system culture settings.

Remarks

Examples

/**
 * This script sets the value of a cell to a date string for January 2, 2023.
 * It writes the day or month first in the string based on system settings.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first cell in the current worksheet.
  const cell = workbook.getActiveWorksheet().getCell(0,0);

  // Get the date format.
  const cultureInfo : ExcelScript.CultureInfo = workbook.getApplication().getCultureInfo();
  const systemDateTimeFormat : ExcelScript.DatetimeFormatInfo = cultureInfo.getDatetimeFormat();
  const shortDatePattern : string = systemDateTimeFormat.getShortDatePattern();

  // Determine if the date should start with the month or day.
  if (shortDatePattern.startsWith("m")) {
    cell.setValue("1/2/2023");
  } else {
    cell.setValue("2/1/2023");
  }
}

Methods

getDateSeparator()	Gets the string used as the date separator. This is based on current system settings.
getLongDatePattern()	Gets the format string for a long date value. This is based on current system settings.
getLongTimePattern()	Gets the format string for a long time value. This is based on current system settings.
getShortDatePattern()	Gets the format string for a short date value. This is based on current system settings.
getTimeSeparator()	Gets the string used as the time separator. This is based on current system settings.

Method Details

getDateSeparator()

Gets the string used as the date separator. This is based on current system settings.

getDateSeparator(): string;

Returns

string

Examples

/**
 * This script writes the current date, month, and year.
 * It uses the system's date separator character.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first cell in the current worksheet.
  const cell = workbook.getActiveWorksheet().getCell(0,0);

  // Get the date separation string.
  const cultureInfo = workbook.getApplication().getCultureInfo();
  const systemDateTimeFormat = cultureInfo.getDatetimeFormat();
  const separator = systemDateTimeFormat.getDateSeparator();

  // Get the current date.
  const currentDate = new Date(Date.now());

  // Write the date using the system's separator character.
  cell.setValue(`${currentDate.getMonth()}${separator}${currentDate.getDate()}${separator}${currentDate.getFullYear()}`);
}

getLongDatePattern()

Gets the format string for a long date value. This is based on current system settings.

getLongDatePattern(): string;

Returns

string

Examples

/**
 * This script returns the system's long date pattern. 
 * This could be used in a Power Automate flow to keep date formatting consistent.
 */
function main(workbook: ExcelScript.Workbook) : string {
    const cultureInfo = workbook.getApplication().getCultureInfo();
    const dateTimeInfo =  cultureInfo.getDatetimeFormat();
    
    return dateTimeInfo.getLongDatePattern();
}

getLongTimePattern()

Gets the format string for a long time value. This is based on current system settings.

getLongTimePattern(): string;

Returns

string

getShortDatePattern()

Gets the format string for a short date value. This is based on current system settings.

getShortDatePattern(): string;

Returns

string

getTimeSeparator()

Gets the string used as the time separator. This is based on current system settings.

getTimeSeparator(): string;

Returns

string

Examples

/**
 * This script writes the current hour, minute, and second.
 * It uses the system's time separator character.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first cell in the current worksheet.
  const cell = workbook.getActiveWorksheet().getCell(0, 0);

  // Get the date separation string.
  const cultureInfo = workbook.getApplication().getCultureInfo();
  const systemDateTimeFormat = cultureInfo.getDatetimeFormat();
  const separator = systemDateTimeFormat.getTimeSeparator();

  // Get the current time.
  const currentTime = new Date(Date.now());

  // Write the date using the system's separator character.
  cell.setValue(`${currentTime.getHours()}${separator}${currentTime.getMinutes()}${separator}${currentTime.getSeconds()}`);
}