Skip to main content

Date operation

TypeScript has an object and method for operating the date, so it can be handled in office scripts as well.

This time, we will use an Office Scripts to explain the systematic operation method of the date together with the sample code.

Get the date​

You can get the date by calling the Date object provided as standard.

If the argument is not specified, the current date will be returned.

Date object definition
/** Date of specified year, month, and day */
const date = new Date(2024, 0, 1);

now.toLocaleDateString(); // 2024/1/1

/** Current date */
const now = new Date();

now.toLocaleDateString(); // Current date

Note that the monthly range is 0 to 11, not 1 to 12.

Date addition / subtraction​

Date object has a method that sets each date, hour, and hour.

Date addition / subtraction
/** Current date */
const now = new Date();

// Change of year
now.setFullYear(now.getFullYear() + 1);
// Month change
now.setMonth(now.getMonth() + 1);
// Change of day
now.setDate(now.getDate() - 1);
// Change of time
now.setHours(now.getHours() + 1);
// Change of minutes
now.setMinutes(now.getMinutes() - 1);
// Change of seconds
now.setSeconds(now.getSeconds() - 1);

Here, if a lead occurs, the unit is automatically processed.

For example, if the hour after calculation is 30, it will be automatically processed at 6:00 the next day.

Find the difference between two Date objects​

With the Date.getTime method, you can receive milliseconds that have passed since January 1, 1970.

It is common to use this to calculate the difference in date from milliseconds.

Find the difference in date
/** Current date */
const now = new Date();

/** New Year */
const gantan = new Date(now.getFullYear(), 0, 1);

/** Date difference (milliseconds) */
const difference = now.getTime() - gantan.getTime();

/** Difference in date (Sunday) */
const differenceDays = Math.floor(difference / 1000 / 60 / 60 / 24);

console.log(`${differenceDays} days have passed since New Year's Day`);

Supplement to Date objects​

The Date object is a mutable variable.

For example, pass a Date object as an argument to a specific function.

At that time, there is no guarantee as before the passed Date object passed after the function processing was completed.

Date object variable
function main(workbook: ExcelScript.Workbook) {
  const now = new Date();

  console.log(now.toLocaleDateString()); // 2022/3/16
  changeMonth(now);
  console.log(now.toLocaleDateString()); // 2022/1/16
}

function changeMonth(date: Date) {
  date.setMonth(0);
}

In the case of the above code, it can be seen that the value of the variable NOW has changed before and after execution of the Changemonth function.

Because the code is simple, you can immediately identify where the NOW has been changed, but as the entire program is enlarged, this is becoming more difficult.

If you pass a Date object to another function, you need to be careful if any changes have been made.