// Sheets · Apps Script

Convert a Unix timestamp to a date in Google Sheets.

Turn an epoch timestamp into a real Date or a formatted string in Apps Script. Covers the two things that go wrong: seconds versus milliseconds, and the timezone the formatted output lands in.

“I have a column of Unix timestamps from an API and I need them as readable dates in my sheet's timezone.”

The script

copy · paste · trigger

epochToDate.gs

Apps Script

// Convert a Unix epoch timestamp (in SECONDS) to a Date object and a
// formatted string in the script's timezone. JS Date wants
// milliseconds, so multiply seconds by 1000.
function epochToDate(epochSeconds) {
  var date = new Date(epochSeconds * 1000);
  var tz = Session.getScriptTimeZone();
  var formatted = Utilities.formatDate(date, tz, 'yyyy-MM-dd HH:mm:ss');
  return { date: date, formatted: formatted };
}

// epochToDate(1717545600)
//   -> { date: <Date>, formatted: '2024-06-05 00:00:00' }
// If your timestamps are already in MILLISECONDS, drop the * 1000.

Generate yours

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

Walkthrough

Seconds, milliseconds, and the year 1970 tell

Unix epoch time is the count of seconds since midnight UTC on 1 January 1970. JavaScript's Date constructor counts milliseconds since that same instant. Those are the same origin but a thousand-fold difference in unit, and that mismatch is the single most common reason this conversion goes wrong. Feed new Date() a count of seconds and it thinks you handed it milliseconds, so it lands a thousand times closer to 1970 than it should.

The symptom is unmistakable once you've seen it. A timestamp that should read 2024 comes out as January 1970, somewhere in the first three weeks. If your converted dates are clustered around 20 January 1970, you forgot the * 1000. If they're somewhere in the far future instead, you multiplied a millisecond timestamp that didn't need it.

There's a quick eyeball test. A current epoch-seconds value is ten digits (it crossed into ten digits in 2001 and stays there until 2286). A current epoch-milliseconds value is thirteen digits. Count the digits in your raw value before you write any code: ten means seconds, multiply; thirteen means milliseconds, don't. APIs are split roughly down the middle on which they hand you, and almost none of them say so in the field name, so checking the digit count beats trusting the docs.

The timezone the string lands in

A Date object has no timezone. It's a single instant, the same moment everywhere on earth. The timezone only enters when you turn that instant into text, and that's where the second surprise lives. Utilities.formatDate(date, timeZone, pattern) takes the zone as an explicit argument, and whatever you pass decides what wall-clock time the string shows. Pass nothing sensible and you'll get a string that's hours off from what the user expects.

Session.getScriptTimeZone() returns the timezone configured in the project's settings (the same one shown under Project Settings in the editor), which usually tracks the spreadsheet's locale. That's the right default for output a human in that sheet will read. If you need a fixed zone regardless of project settings, pass a string like 'America/New_York' or 'UTC' straight into formatDate instead. Hardcoding 'UTC' is the honest choice when the value is a server log and you want it unambiguous.

One detail that saves a debugging session: the pattern string is Java's SimpleDateFormat, not strftime. So it's yyyy-MM-dd HH:mm:ss, with a capital MM for month and lowercase mm for minute, capital HH for 24-hour and lowercase hh for 12-hour. Mixing those up is how you end up with the month where the minutes should be. I've shipped that bug, watched a report show '2024-48-05', and spent a minute confused before remembering Apps Script speaks Java date patterns, not C ones.

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

My dates all show as January 1970. What went wrong?: You passed epoch seconds to new Date() without multiplying by 1000. Date wants milliseconds. A seconds value interpreted as milliseconds lands roughly 54 years too early, which puts it in January 1970.
How do I know if my timestamp is seconds or milliseconds?: Count the digits. A current timestamp in seconds is 10 digits; in milliseconds it's 13. Ten digits, multiply by 1000. Thirteen digits, use it as-is.
Why is the time a few hours off from what I expect?: The Date is a correct instant; the offset comes from the timezone you formatted in. Pass Session.getScriptTimeZone() for the project's zone, or a fixed string like 'UTC' for unambiguous output.
Can I write the Date straight into a cell instead of a string?: Yes. setValue(date) with a real Date object lets Sheets apply the cell's own date format, and the value stays sortable and filterable. Only format to a string when you specifically need text, like in an email body.