Converting timestamps to dates formula

In Apps Script, it’s easy 
var timeStamp = new Date().getTime();
var date = new Date(timestamp);

But sheets doesn’t use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs represented as a fraction of a day (an idea copied from Excel – known as DateValue + TimeValue) – so all these are equivalent

 date timestamp   datevalue+timevalue
 20/05/2015 11:31:05  1432121465149  42144.4799189815
Page Content hide
1 The Epoch
2 formula for converting a JS timestamp to a date =yourTimestamp/1000/60/60/24 + date (1970,1,1) formula for converting a date to a JS timestamp =(yourDate-date(1970,1,1))*1000*60*60*24 For more like this see Google Apps Scripts Snippets2 ways to create and preserve formulas with fiddler for Google Apps Script A fourth way to preserve and create formulas with Fiddler for Sheets, plus some more new methods A functional approach to updating master sheet with Fiddler A recursive extend function for Apps Script A third way to preserve formulas with fiddler, plus 2 new methods A webapp to share copies of the contents of folders Abstracting services with closures Add-on spinner Addressing namespace and library methods from google.script.run Anonymous user registration with the Apps Script PropertiesService Apps for Office - binding example comparison Apps Script as a proxy Apps Script const scoping problems Apps Script server side to client side htmlservice progress reporting using CacheService Caching, property stores and pre-caching Calculate contrasting font colors for Sheets. Calculating image dimensions in server side apps script Calculating the last day of a given weekday in the month Canvasser Chaining JavaScript Changing class properties dynamically Checking the argument types in Apps Script Cleaning up a document format Cleaning up heading levels Color scales, custom schemes and proxies with Apps Script Colorize sentiment in a Google document Column numbers to characters Composing functions and functional programming Configurable canvas meter Content oriented color mixing with Apps Script Convert any file with Apps Script Convert JSON to XML Converting SVG to PNG with JavaScript Copying canvas and svg images from your Add-on Copying to new host location Copying to new host location Counting script and library usage Create GraphML markups from Apps Script Create sha1 signatures with apps script Creating a key digest to use for a cache key or to compare content Creating a pile of files list from Google Drive Creating and working with transposed sheet data arrays Cross Origin Resource sharing (CORS) CryptoJS libraries for Google Apps Script Custom checking for exponential backoff Data wrangling with named columns in Google Spreadsheet Dealing with objects that are too large for the property or cache store Detect fake news with Google Fact Check tools Detecting Spreadsheet tables automatically with Google Apps Script Direction minimizer - other usages Do something useful with GAS in 5 minutes A simple invoice app Copy databases using OAuth2 Copying databases Fusion and crossfilter Get analytics profiles in a sheet Getting Analytics Pageviews into a sheet Graduate to a database Summarize email thread recipients to a spreadsheet Use a spreadsheet as a database in 5 minutes Dynamically creating tables with clusterize.js EasyCron Library ES6 JavaScript features Exponential backoff Exponential backoff for promises Fiddler - A functional approach to fiddling with sheet data Fiddler and rangeLists Fiddler now supports joins to merge matching columns from multiple sheets Fiddling with text fields that look like dates Filling ranges in Google Sheets Find nearest matches in color schemes with Apps Script Finding a Drive App folder by path Finding where Drive hosting is being used in Sites Fix Apps Script file order problems with Exports Flattening an object with dot syntax Flattening and unflattening objects to spreadsheets Formatting GraphQL queries Formatting sheet column data with fiddler From notes to frequencies and back again From Xml to JSON Generating and managing random lists with JavaScript and Apps Script Generating coupon codes with expiry dates Generating test data for sheets and tables Get GAS library info Getting an htmlservice template from a library Getting insights into Sheets performance Google Drive as cache Header formatting with fiddler Highlight duplicate rows in a sheet - map and reduce Highlight duplicate rows in a sheet - map, filter and every How to determine what kind of object something is in Apps Script How to get stats about youtube videos in your channel with apps script How to pass non stringifyable objects to html service How to transpose spreadsheet data with apps script Identify duplicates on Drive Identifying hosted files Implementing a client side progress bar reporting on server side work for htmlService Importing Predictwise data Improved namespace pattern for Apps Script Including the stack in custom errors Iterator magic - Splitting an array into chunks JavaScript closures - how, where and why JavaScript currying and functional programming JavaScript currying and functional programming - even more JavaScript recursion primer JavaScript snippet to create a default method for an object JavaScript typo detection with proxies JSONP and JSON and Google Apps Script webapps Loading large JSON datasets into BigQuery with Apps Script Logging differences in strings in Apps Script Manipulating PDFS in Apps Script Markup HTML from JSON with Apps Script and JavaScript Measuring library load speed Migrating user and script properties Minimizing maps directionfinder api calls More client server code sharing More recursion - parents and children More sheet data fiddling Multiple inserts in Fusion Tables Namespaces in libraries and scripts Normalizing the header level of blank paragraphs Obfuscate text with Apps Script Optimizing sheet formatting with Apps Script Optimizing showing and hiding rows and columns Organizing asynchronous calls to google.script.run Organizing parallel streams of server calls with google.script.run promises Parallel process orchestration with HtmlService Passing data to html service Patching site html Populating sheets with API data using a Fiddler Promises and async class constructors in Apps Script Proxy jsonp Proxy magic with Google Apps Script Pseudo binding in HTML service Queuing asynchronous tasks with rate limit and concurrency constraints Qottle recipe: Managing asynchronous queues with duplicates Qottle recipe: How to manage an asynchronous polling queue Random and fake test data in Sheets with Google Apps Script Recursive async functions Removing duplicate paragraphs Reporting file, function and line number in Apps Script Resumable uploads - writing large files to Drive with Apps Script Reusing html stuff between Apps Script projects Roughly matching text Serving apps script to JavaScript app Sharing cache between Google Workspace projects Sharing code between client and server Shortcut for adding nested properties to a JavaScript object Simple but powerful Apps Script Unit Test library Simple server side polling Sorting Google Sheet DisplayValues Squeezing more into (and getting more out of) Cache services Styling Gmail html tables Summarizing emails to a sheet SunCalc Super simple cipher library for Apps Script encryption and decryption TimeSimmer : An adjustable timer for apps that need to speed up or slow down time Transform dates for add-on transfer Transposing sheet data Traversing a tree Unique values with data fiddler Unnesting data to sheet values Untangling with promises Use a proxy to catch invalid property access Use Drive properties to find app files Use promise instead of callback for settimeout Using Advanced Drive service to convert files Using Apps Script for xml json conversion Using array formulas to improve performance Using crossfilter with Google Apps Script Using D3 in server side Gas Emulating worksheet functions in apps script with d3 Using es6 promises server side in Apps Script Using Es6 with Apps Script Using exponential backoff with github api - dealing with data "in preparation" Using Google sheets via Bigquery from Apps Script Using named locks with Google Apps Scripts Using promises to orchestrate Html service polling Using promises with apps script Using the Itunes API with Apps Script Using the slideshare API from Apps Script Using timing functions to get insight into Sheets Watching docs for changes Watching for server side changes from the client html service What JavaScript engine is Apps Script running on? Why Base64 Zipping to make stuff fit in cache or properties service. Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available. Share with your network
3 =yourTimestamp/1000/60/60/24 + date (1970,1,1) formula for converting a date to a JS timestamp =(yourDate-date(1970,1,1))*1000*60*60*24 For more like this see Google Apps Scripts Snippets2 ways to create and preserve formulas with fiddler for Google Apps Script A fourth way to preserve and create formulas with Fiddler for Sheets, plus some more new methods A functional approach to updating master sheet with Fiddler A recursive extend function for Apps Script A third way to preserve formulas with fiddler, plus 2 new methods A webapp to share copies of the contents of folders Abstracting services with closures Add-on spinner Addressing namespace and library methods from google.script.run Anonymous user registration with the Apps Script PropertiesService Apps for Office - binding example comparison Apps Script as a proxy Apps Script const scoping problems Apps Script server side to client side htmlservice progress reporting using CacheService Caching, property stores and pre-caching Calculate contrasting font colors for Sheets. Calculating image dimensions in server side apps script Calculating the last day of a given weekday in the month Canvasser Chaining JavaScript Changing class properties dynamically Checking the argument types in Apps Script Cleaning up a document format Cleaning up heading levels Color scales, custom schemes and proxies with Apps Script Colorize sentiment in a Google document Column numbers to characters Composing functions and functional programming Configurable canvas meter Content oriented color mixing with Apps Script Convert any file with Apps Script Convert JSON to XML Converting SVG to PNG with JavaScript Copying canvas and svg images from your Add-on Copying to new host location Copying to new host location Counting script and library usage Create GraphML markups from Apps Script Create sha1 signatures with apps script Creating a key digest to use for a cache key or to compare content Creating a pile of files list from Google Drive Creating and working with transposed sheet data arrays Cross Origin Resource sharing (CORS) CryptoJS libraries for Google Apps Script Custom checking for exponential backoff Data wrangling with named columns in Google Spreadsheet Dealing with objects that are too large for the property or cache store Detect fake news with Google Fact Check tools Detecting Spreadsheet tables automatically with Google Apps Script Direction minimizer - other usages Do something useful with GAS in 5 minutes A simple invoice app Copy databases using OAuth2 Copying databases Fusion and crossfilter Get analytics profiles in a sheet Getting Analytics Pageviews into a sheet Graduate to a database Summarize email thread recipients to a spreadsheet Use a spreadsheet as a database in 5 minutes Dynamically creating tables with clusterize.js EasyCron Library ES6 JavaScript features Exponential backoff Exponential backoff for promises Fiddler - A functional approach to fiddling with sheet data Fiddler and rangeLists Fiddler now supports joins to merge matching columns from multiple sheets Fiddling with text fields that look like dates Filling ranges in Google Sheets Find nearest matches in color schemes with Apps Script Finding a Drive App folder by path Finding where Drive hosting is being used in Sites Fix Apps Script file order problems with Exports Flattening an object with dot syntax Flattening and unflattening objects to spreadsheets Formatting GraphQL queries Formatting sheet column data with fiddler From notes to frequencies and back again From Xml to JSON Generating and managing random lists with JavaScript and Apps Script Generating coupon codes with expiry dates Generating test data for sheets and tables Get GAS library info Getting an htmlservice template from a library Getting insights into Sheets performance Google Drive as cache Header formatting with fiddler Highlight duplicate rows in a sheet - map and reduce Highlight duplicate rows in a sheet - map, filter and every How to determine what kind of object something is in Apps Script How to get stats about youtube videos in your channel with apps script How to pass non stringifyable objects to html service How to transpose spreadsheet data with apps script Identify duplicates on Drive Identifying hosted files Implementing a client side progress bar reporting on server side work for htmlService Importing Predictwise data Improved namespace pattern for Apps Script Including the stack in custom errors Iterator magic - Splitting an array into chunks JavaScript closures - how, where and why JavaScript currying and functional programming JavaScript currying and functional programming - even more JavaScript recursion primer JavaScript snippet to create a default method for an object JavaScript typo detection with proxies JSONP and JSON and Google Apps Script webapps Loading large JSON datasets into BigQuery with Apps Script Logging differences in strings in Apps Script Manipulating PDFS in Apps Script Markup HTML from JSON with Apps Script and JavaScript Measuring library load speed Migrating user and script properties Minimizing maps directionfinder api calls More client server code sharing More recursion - parents and children More sheet data fiddling Multiple inserts in Fusion Tables Namespaces in libraries and scripts Normalizing the header level of blank paragraphs Obfuscate text with Apps Script Optimizing sheet formatting with Apps Script Optimizing showing and hiding rows and columns Organizing asynchronous calls to google.script.run Organizing parallel streams of server calls with google.script.run promises Parallel process orchestration with HtmlService Passing data to html service Patching site html Populating sheets with API data using a Fiddler Promises and async class constructors in Apps Script Proxy jsonp Proxy magic with Google Apps Script Pseudo binding in HTML service Queuing asynchronous tasks with rate limit and concurrency constraints Qottle recipe: Managing asynchronous queues with duplicates Qottle recipe: How to manage an asynchronous polling queue Random and fake test data in Sheets with Google Apps Script Recursive async functions Removing duplicate paragraphs Reporting file, function and line number in Apps Script Resumable uploads - writing large files to Drive with Apps Script Reusing html stuff between Apps Script projects Roughly matching text Serving apps script to JavaScript app Sharing cache between Google Workspace projects Sharing code between client and server Shortcut for adding nested properties to a JavaScript object Simple but powerful Apps Script Unit Test library Simple server side polling Sorting Google Sheet DisplayValues Squeezing more into (and getting more out of) Cache services Styling Gmail html tables Summarizing emails to a sheet SunCalc Super simple cipher library for Apps Script encryption and decryption TimeSimmer : An adjustable timer for apps that need to speed up or slow down time Transform dates for add-on transfer Transposing sheet data Traversing a tree Unique values with data fiddler Unnesting data to sheet values Untangling with promises Use a proxy to catch invalid property access Use Drive properties to find app files Use promise instead of callback for settimeout Using Advanced Drive service to convert files Using Apps Script for xml json conversion Using array formulas to improve performance Using crossfilter with Google Apps Script Using D3 in server side Gas Emulating worksheet functions in apps script with d3 Using es6 promises server side in Apps Script Using Es6 with Apps Script Using exponential backoff with github api - dealing with data "in preparation" Using Google sheets via Bigquery from Apps Script Using named locks with Google Apps Scripts Using promises to orchestrate Html service polling Using promises with apps script Using the Itunes API with Apps Script Using the slideshare API from Apps Script Using timing functions to get insight into Sheets Watching docs for changes Watching for server side changes from the client html service What JavaScript engine is Apps Script running on? Why Base64 Zipping to make stuff fit in cache or properties service. Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available. Share with your network
4 formula for converting a date to a JS timestamp
5 Share with your network

The Epoch

Unix-like systems use 1st Jan 1970 as zero hour (often referred to as the epoch) for dates, so a JavaScript timestamp (13 digits) is the number of milliseconds since then. Prior to then is a negative number. Unix timestamps (10 digit) use the same base, but are the number of seconds rather than milliseconds.

Knowing these two base dates makes converting back and forwards with a Sheets (or Excel) formula easy.

formula for converting a JS timestamp to a date 

=yourTimestamp/1000/60/60/24 + date (1970,1,1)

formula for converting a date to a JS timestamp

=(yourDate-date(1970,1,1))*1000*60*60*24
For more like this see Google Apps Scripts Snippets
Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.