Basics
Dates, time zones, and Utilities.formatDate
A Date holds one instant in time. Use Utilities.formatDate(date, timeZone, pattern) and Session.getScriptTimeZone() to format it in the right time zone.
1 of 8
A Date is one instant in time
new Date() gives you the moment that line of code runs. A Date object holds a single instant, stored as a count of milliseconds since the start of 1970.
A Date does not carry a time zone with it. The same instant reads as 5:00 PM in New York and 10:00 PM in London. So one Date can show different times to two people, which is why a date can look a day off.
new Date(2026, 0, 15) builds a particular day. Months are counted from 0, so 0 is January and 11 is December. Days of the month are counted from 1, as you would expect.
- date.getTime() is the instant as a plain number of milliseconds.
- date.getFullYear(), getMonth(), getDate(), getHours() read the parts in the project time zone set in Project Settings, which Session.getScriptTimeZone() returns.
- getMonth() returns 0 for January, so add 1 before showing it to anyone.
2 of 8
The project time zone
Every Apps Script project has a time zone setting. Open Project Settings with the gear icon in the left sidebar of the editor and you will see it near the top. Fix it there if it is wrong, because a new project does not always guess your country correctly.
Session.getScriptTimeZone() returns that setting as text, for example "America/New_York". Pass it wherever a time zone is wanted, so your formatting follows the project setting instead of a value typed into the code.
A spreadsheet has its own time zone under File, then Settings. Make the two match. When they differ, a date the script writes can display an hour or a whole day away from what you expect.
3 of 8
Turn a Date into text with Utilities.formatDate
Utilities.formatDate(date, timeZone, pattern) turns a Date into a string. The pattern is made of letters, and each letter stands for one part of the date. Anything that is not a pattern letter, such as a comma or a dash, is kept as it is.
All three arguments are required, and the second argument must be a time zone string such as Session.getScriptTimeZone(). Passing the format pattern as the second argument or leaving timeZone undefined throws "Exception: Invalid argument: timeZone. Should be of type: String".
Capital letters matter. MM is the month and mm is the minutes. HH is the hour on a 24 hour clock and hh is the hour on a 12 hour clock. Use yyyy for the year and dd for the day of the month. YYYY is the week year, which gives the wrong year for some dates around 1 January, and DD is the day of the year. The letters follow Java SimpleDateFormat, described at https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
- 'yyyy-MM-dd' gives 2026-03-14. Good for file names, and it sorts correctly as text.
- 'MMM d, yyyy' gives Mar 14, 2026.
- 'EEEE' gives Saturday, the name of the day.
- 'h:mm a' gives 4:05 PM.
- 'yyyy-MM-dd HH:mm' gives 2026-03-14 16:05, on a 24 hour clock.
function logTodayThreeWays() {
const timeZone = Session.getScriptTimeZone();
const now = new Date();
Logger.log(Utilities.formatDate(now, timeZone, 'yyyy-MM-dd'));
Logger.log(Utilities.formatDate(now, timeZone, 'EEEE, MMM d, yyyy'));
Logger.log(Utilities.formatDate(now, timeZone, 'h:mm a'));
}4 of 8
Dates that come out of a Sheet
When a cell holds a real Sheets date, getValues() and getValue() hand you a Date object, ready to use. Set the column's number format to Date and enter a date, not text.
A cell that only looks like a date can hold a string instead. Do not pass that string to new Date(): date text can be ambiguous, and an ISO date-only string is interpreted as midnight UTC rather than in your spreadsheet's calendar time zone. Reject text and fix the cell instead.
Use the spreadsheet time zone when formatting a date read from that spreadsheet. That preserves the calendar day the person entered, even if the script project uses a different time zone.
function logFirstDueDate() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('Tasks');
if (!sheet) throw new Error('Add a Sheet tab named Tasks before running this function.');
const dueDate = sheet.getRange('B2').getValue();
if (!(dueDate instanceof Date) || isNaN(dueDate.getTime())) {
throw new Error('Enter a real date in B2, not text, and format the cell as a date.');
}
Logger.log('Due ' + Utilities.formatDate(dueDate, spreadsheet.getSpreadsheetTimeZone(), 'yyyy-MM-dd'));
}5 of 8
Add and subtract days
Two ways work well. setDate(getDate() + 7) moves a Date forward one week and rolls over into the next month or year for you. Or do the arithmetic in milliseconds: new Date(date.getTime() + 7 * 24 * 60 * 60 * 1000).
Change a copy, not the original. setDate() alters the Date object in place, so a Date you handed to something else moves too. new Date(other.getTime()) makes a fresh copy to work on.
The two ways differ on the two days a year when the clocks change, because a day of milliseconds is always 24 hours while a calendar day is sometimes 23 or 25. Prefer setDate() for anything a person reads.
function logDueDateAndDaysRemaining() {
const timeZone = Session.getScriptTimeZone();
const today = new Date();
const dueDate = new Date(today.getTime());
dueDate.setDate(dueDate.getDate() + 7);
const millisecondsPerDay = 24 * 60 * 60 * 1000;
const daysRemaining = Math.round((dueDate.getTime() - today.getTime()) / millisecondsPerDay);
Logger.log('Due ' + Utilities.formatDate(dueDate, timeZone, 'EEEE, MMM d, yyyy'));
Logger.log(daysRemaining + ' days remaining.');
}6 of 8
The start and the end of a day
Calendar searches and date filters need a start and an end. Today as a Date is this instant, not the whole day, so a search from now until now finds nothing.
Build the start and end times. Copy the date and call setHours(0, 0, 0, 0) for the first instant of the day, then copy again and call setHours(23, 59, 59, 999) for the last. setHours() sets hours, minutes, seconds, and milliseconds in one call.
For a single whole day on a calendar, CalendarApp.getDefaultCalendar().getEventsForDay(date) does this work for you. Build the start and end yourself when the window is not one whole day.
function logTodaysWindow() {
const timeZone = Session.getScriptTimeZone();
const startOfDay = new Date();
startOfDay.setHours(0, 0, 0, 0);
const endOfDay = new Date(startOfDay.getTime());
endOfDay.setHours(23, 59, 59, 999);
Logger.log('From ' + Utilities.formatDate(startOfDay, timeZone, 'yyyy-MM-dd HH:mm:ss'));
Logger.log('To ' + Utilities.formatDate(endOfDay, timeZone, 'yyyy-MM-dd HH:mm:ss'));
}7 of 8
Compare two dates
dateA === dateB is false even when both hold the same instant, because they are two separate objects and === asks whether they are the same object. Compare the numbers instead, with dateA.getTime() === dateB.getTime().
Less than and greater than do work straight on Date objects, so dueDate < today reads well and is correct. getTime() on both sides works for every comparison, including equality.
To compare days rather than instants, format both with "yyyy-MM-dd" and compare the two strings. Two times on the same afternoon then count as equal.
8 of 8
Write a date into a cell
setValue(new Date()) puts a real date in the cell. Sheets shows it using whatever format the cell has, and it sorts, filters, and works in date formulas like any date typed by hand.
Writing Utilities.formatDate(...) into the cell puts text there instead. It looks the same on screen, but it sorts alphabetically and date formulas treat it as text.
So write the Date object into the sheet, and keep formatDate for text people read: an email body, a document, a log line, or a file name.
function stampCheckedTime() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
if (!sheet) throw new Error('Add a Sheet tab named Tasks before running this function.');
sheet.getRange('D2').setValue(new Date());
Logger.log('Wrote the current date and time into D2 of the Tasks tab.');
}Remember
The short version
- A Date is one instant, and the time zone sets how that instant is shown as text.
- Format with Utilities.formatDate(date, Session.getScriptTimeZone(), pattern) so output follows the project setting.
- Set a sheet date column's number format and time zone, then use the real Date value it returns rather than parsing date text.
- Copy a Date before calling setDate() or setHours(), because both change the object in place.
- Write a Date into a cell to keep it a real date, and use formatted text only for people to read.
Keep going
Put it to work
- Task guide Run an Apps Script function every day
Make a function run on its own once a day. Run the setup function once, and from then on Apps Script calls the reminder function each day around the hour you chose.
- Task guide Create Calendar events from Sheet rows
Turn rows on an Events tab into events on your Google Calendar. The script creates an event for each row that has no event ID yet, then writes the new ID back into the row so a later run skips it.
- Task guide Email yourself tomorrow's calendar agenda
Get an email each evening listing tomorrow's events on your main Google Calendar. Run the setup function once to install the daily trigger.
- Task guide Add a timestamp when a row changes
When you change a status cell, the script writes the current date and time into the same row. You save the script and nothing else needs to be installed.
- Basics Rows, columns, and 2D arrays in Sheets scripts
A range is a rectangle of cells. getValues() returns that rectangle as an array of rows, and each row is an array of cell values.
- Basics Common Apps Script errors and what they mean
Apps Script error messages say what failed, for example TypeError: Cannot read properties of null (reading 'getRange') or ReferenceError. Find the exact message below for its cause and fix.