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

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.