Working with dates

JavaScript (TypeScript) has a built-in Date object for working with dates, and you can use it the same way in Office Scripts.

This article uses Office Scripts to give a systematic overview of working with dates, with sample code.

Get a date

You can get a date by creating a Date object with new Date().

If you don’t specify any arguments, a Date object that represents the current date and time is created.

Defining Date objects

/** Date with the year, month, and day specified */
const date = new Date(2024, 0, 1);

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

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

now.toLocaleDateString(); // 3/16/2022 (current date)

Note that months range from 0 to 11, not 1 to 12 (0 is January and 11 is December).

Also, the output format of toLocaleDateString depends on the locale (language and region settings) of the environment. The comments in the sample show examples from a US English environment.

Add to and subtract from dates

Date objects have methods for setting each of the year, month, day, hours, minutes, and seconds.

Adding to and subtracting from dates

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

// Change the year
now.setFullYear(now.getFullYear() + 1);
// Change the month
now.setMonth(now.getMonth() + 1);
// Change the day
now.setDate(now.getDate() - 1);
// Change the hours
now.setHours(now.getHours() + 1);
// Change the minutes
now.setMinutes(now.getMinutes() - 1);
// Change the seconds
now.setSeconds(now.getSeconds() - 1);

If a value overflows here, it’s automatically carried over to the next larger unit.

For example, if the hours come to 30 after the calculation, the result is automatically treated as 6:00 the next day.

Be careful when adding months

If you add a month to a date at the end of a month, the nonexistent date rolls over, and you may get an unexpected result. For example, running setMonth(now.getMonth() + 1) on January 31, 2024 gives March 2, 2024, because February 31 doesn’t exist.

Find the difference between two Date objects

The Date.getTime method returns the number of milliseconds that have elapsed since 00:00:00 UTC on January 1, 1970.

A common approach is to use this to calculate the difference between dates from the milliseconds.

Finding the difference between dates

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

/** New Year's Day */
const newYearsDay = new Date(now.getFullYear(), 0, 1);

/** Difference between the dates (milliseconds) */
const difference = now.getTime() - newYearsDay.getTime();

/** Difference between the dates (days) */
const differenceDays = Math.floor(difference / 1000 / 60 / 60 / 24);

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

More about Date objects

Date objects are mutable.

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

In that case, there’s no guarantee that the Date object will be the same after the function finishes as it was before you passed it.

Mutability of Date objects

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

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

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

In the code above, you can see that the value of the variable now is different before and after the changeMonth function runs.

Because the code is simple, you can quickly find where now was changed, but as a program grows, this gets harder and harder.

When you pass a Date object to another function, you need to watch out for changes to it. If you don’t want the original value to change, it’s safer to pass a copy, as in new Date(now.getTime()).