ScriptdrillDates in Apps Script
Date cells come out of getValues as JavaScript Date objects. Create one with new Date(2026, 10, 3) (months are 0-based, so 10 is November), add days with d.setDate(d.getDate() + 7), get the difference with (b - a) / 86400000, and compare with getTime(). Write Date objects back with setValues and the cell stays a date.
Syntax
new Date() | new Date(year, monthIndex, day) | date.getTime() | date.setDate(date.getDate() + n)| Part | What it is |
|---|---|
monthIndex | 0 = January, 11 = December |
getTime() | Milliseconds since 1970; use for comparisons and differences |
86400000 | Milliseconds in a day (24 * 60 * 60 * 1000) |
Session.getScriptTimeZone() | Zone used for new Date() display and formatDate |
Examples
const due = new Date(2026, 10, 3);
const reminder = new Date(due);
reminder.setDate(reminder.getDate() + 7);
Logger.log(Utilities.formatDate(reminder, "GMT", "yyyy-MM-dd"));Result: 2026-11-10
const a = new Date(2026, 0, 1);
const b = new Date(2026, 11, 31);
Logger.log(Math.round((b - a) / 86400000));Result: 364
const sheet = SpreadsheetApp.getActiveSheet();
const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
const today = new Date();
today.setHours(0, 0, 0, 0);
const overdue = rows.filter(r => r[1] instanceof Date && r[1] < today);
Logger.log(overdue.length);Result: Number of rows whose date in column B is before today
const lastDay = new Date(2026, 2, 0);
Logger.log(lastDay.getDate());Result: 28 (day 0 of March is the last day of February; 2026 is not a leap year)
For a leap year such as 2028 the same line gives 29.
Common mistakes
- new Date(2026, 11, 3) gives December instead of November: months are 0-based; use 10 for November.
- new Date("03/11/2026") is parsed as March 11: avoid parsing ambiguous strings; build from parts or use ISO yyyy-MM-dd.
- A date compared with === is never equal: Date objects are compared by reference; compare getTime() values.
- Dates shift by one day after writing: the sheet and script time zones differ; align them in Project Settings and File, Settings.
Practice Dates in Apps Script
These drills check your answer on live data, free, no account needed.
- Add days to a date`isoDate` is a calendar date like `"2024-01-30"`. Return the date `n` days later (earlier when `n` is negative) in the same `YYYY-MM-DD` form. Work in UTC so the answer does not depend on the time zone of the computer, and get leap years right.
- Days between two dates`a` and `b` are calendar dates like `"2024-01-31"`. Return the number of days from `a` to `b`: positive when `b` is later, negative when it is earlier, 0 for the same date. The answer must be right in every time zone, including across daylight saving changes.
- Last day of the monthReturn the number of the last day of a month (28, 29, 30 or 31). `month` runs from 1 for January to 12 for December. Leap years follow the Gregorian rules: every 4 years, except century years, unless divisible by 400.
Questions
How do I get today without the time?
const d = new Date(); d.setHours(0, 0, 0, 0); then compare with other dates at midnight.
Why does a date cell come back as a number sometimes?
The cell is formatted as a number or text instead of a date. Sheets stores dates as serial days; with a date format, getValues returns a Date object.
Related
- Utilities.formatDateFormat dates in Google Apps Script with Utilities.formatDate: the time zone argument, Java pattern letters like yyyy MM dd HH mm, month names, MM versus mm.
- Create calendar eventsCreate Google Calendar events from a sheet with Apps Script: CalendarApp.createEvent with start and end dates, all-day events, guests, avoiding duplicates.
- Time-driven triggersSchedule Google Apps Script with time-driven triggers: create them in the Triggers panel or with ScriptApp.newTrigger, run daily, hourly or weekly, delete.
- getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.