Why does my date column show ########## after I run the script?

The column is too narrow to display the formatted date. The value and format are correct — widen the column or call sheet.autoResizeColumn(columnIndex) at the end of your function.

setNumberFormat changed the display but DATEDIF still returns #VALUE!

Check whether the cells contain text strings that look like dates rather than real date values. If the column was imported from CSV or copied from another app, cells may already be text. Use VALUE() or DATEVALUE() in a helper column to convert them first, then apply setNumberFormat.

Utilities.formatDate returns a date one day earlier than expected.

This is almost always a timezone mismatch. The Date object coming out of getValues() is in UTC, and if your script timezone is behind UTC the formatted output shifts back by a day. Pass SpreadsheetApp.getActive().getSpreadsheetTimeZone() as the timezone argument instead of Session.getScriptTimeZone() and the day boundary aligns correctly.

How do I apply a locale-aware format like dd/MM/yyyy instead of MM/dd/yyyy?

Pass the token string you want directly: setNumberFormat('dd/MM/yyyy') or Utilities.formatDate(date, tz, 'dd/MM/yyyy'). Neither method infers locale from the spreadsheet's locale setting — you have to spell the order out explicitly in the pattern string.

// Sheets · Apps Script

Change the date format of a column in Google Sheets.

How to reformat a date column in Google Sheets using Apps Script — setNumberFormat to change display, Utilities.formatDate to convert to text strings — with code and the gotchas that break downstream formulas.

“I need to reformat dates in a Sheets column from script so the output looks right and my other formulas still work.”

The script

copy · paste · trigger

formatDateColumn.gs

Apps Script

// Format column B as MM/dd/yyyy (display only — keeps real date values)
// Switch to Utilities.formatDate if you need plain text strings instead
function formatDateColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();

  // setNumberFormat: changes how the date looks, value stays a Date
  var range = sheet.getRange(2, 2, lastRow - 1, 1);
  range.setNumberFormat('MM/dd/yyyy');

  // Utilities.formatDate: converts to a text string — use only if you
  // need plain strings and don't need DATEIF / date arithmetic downstream
  var tz = Session.getScriptTimeZone();
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] instanceof Date) {
      values[i][0] = Utilities.formatDate(values[i][0], tz, 'MM/dd/yyyy');
    }
  }
  // range.setValues(values); // uncomment ONLY if you want text strings
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Two methods, one choice you have to make upfront

Google Sheets stores dates as serial numbers internally — a floating-point count of days since December 30, 1899. When you call setNumberFormat on a range, you are telling Sheets how to render that number. The underlying value stays a real date, so DATEDIF, date arithmetic, and pivot table grouping all keep working. That is the right call for 90% of cases.

Utilities.formatDate does something different: it converts the Date object JavaScript hands you into a plain string, which then gets written back to the cell. The cell now holds text, not a date. DATEDIF will return a #VALUE! error, and sorting will be lexicographic. I have burned a solid afternoon debugging a broken pivot because I reached for Utilities.formatDate out of habit when setNumberFormat was all I needed.

Format tokens that actually matter

Both methods use the same token alphabet for common patterns. yyyy is four-digit year; yy is two-digit. MM is zero-padded month (01–12); M is unpadded. dd is zero-padded day; d is unpadded. For time: HH is 24-hour hour, hh is 12-hour, mm is minutes, ss is seconds.

A few tokens bite people: a single M in Utilities.formatDate produces the month number without padding, but in setNumberFormat it can conflict with minute tokens if you mix date and time in the same pattern. If your pattern includes both date and time parts, test it — the parser is not always obvious. For ISO 8601 output (yyyy-MM-dd) both methods produce identical-looking strings, but only setNumberFormat keeps the cell a real date.

The timezone argument in Utilities.formatDate is not optional in practice. Omitting it does not default to UTC; it uses the script's time zone, which is whatever the project was created under. Session.getScriptTimeZone() is the safe call; SpreadsheetApp.getActive().getSpreadsheetTimeZone() gives you the sheet's own timezone, which is what you usually want when the sheet owner is in a different region than the script project.

Applying the format to a whole column efficiently

The naive approach — loop over every row and call setNumberFormat once per cell — is slow because each Sheets API call has latency. getRange(startRow, column, numRows, 1) selects the entire column slice in one call, and setNumberFormat on that range applies to all rows in a single round-trip. For a 10,000-row column the difference is roughly 30 seconds versus under a second.

For Utilities.formatDate, you have to read the values first (getValues, one call), process them in memory, then write back (setValues, one call). The for loop in the snippet checks instanceof Date before formatting — cells that are already empty strings or numbers will not throw, which matters when your column has gaps.

getLastRow() returns the last row with any content in the sheet, not the last row in the column. If your date column ends before other columns, that is fine — the extra rows in the range will just be empty and the instanceof Date guard catches them.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my date column show ########## after I run the script?: The column is too narrow to display the formatted date. The value and format are correct — widen the column or call sheet.autoResizeColumn(columnIndex) at the end of your function.
setNumberFormat changed the display but DATEDIF still returns #VALUE!: Check whether the cells contain text strings that look like dates rather than real date values. If the column was imported from CSV or copied from another app, cells may already be text. Use VALUE() or DATEVALUE() in a helper column to convert them first, then apply setNumberFormat.
Utilities.formatDate returns a date one day earlier than expected.: This is almost always a timezone mismatch. The Date object coming out of getValues() is in UTC, and if your script timezone is behind UTC the formatted output shifts back by a day. Pass SpreadsheetApp.getActive().getSpreadsheetTimeZone() as the timezone argument instead of Session.getScriptTimeZone() and the day boundary aligns correctly.
How do I apply a locale-aware format like dd/MM/yyyy instead of MM/dd/yyyy?: Pass the token string you want directly: setNumberFormat('dd/MM/yyyy') or Utilities.formatDate(date, tz, 'dd/MM/yyyy'). Neither method infers locale from the spreadsheet's locale setting — you have to spell the order out explicitly in the pattern string.

// one good script a week