How to convert a Unix timestamp to a date in Excel and Google Sheets

Excel stores dates as sequential serial numbers, so each date is a count of days, and the time of day is the fraction after the decimal point. Unix time counts seconds from January 1, 1970 UTC. That date is serial 25569 in the 1900 date system. For this moment, the serial comes out to 46176.77083, and in the 1904 date system it's 44714.77083. If cell A2 holds 1780511400 in seconds, the first line below returns its serial number. The second line does the same with the fixed number, and the third is for a cell that holds milliseconds.

=A2/86400+DATE(1970,1,1)
=A2/86400+25569
=A2/86400000+DATE(1970,1,1)

Each formula returns a serial number. If the cell shows a plain number, apply a date and time format. DATE returns the serial for January 1, 1970 in whichever date system the workbook uses, but 25569 only fits the 1900 system. In a 1904 workbook, the fixed version lands on the wrong year.

Going back helps when a date column has to match an API filter that wants a timestamp. Start with a cell that holds a date and time in UTC. This formula subtracts the epoch serial, multiplies by 86400 to get seconds, and rounds the result.

=ROUND((A2-DATE(1970,1,1))*86400,0)

ROUND removes the tiny floating point error that decimal fractions of a day leave behind. Without it, the result can land a hair under a whole second, and a function that cuts off decimals then returns one second less than you meant.

Google Sheets has a shortcut for the forward trip. EPOCHTODATE reads seconds by default, a second argument of 2 means milliseconds, and 3 means microseconds. Both lines below return a UTC datetime. The first reads A2 as seconds, and the second reads it as milliseconds.

=EPOCHTODATE(A2)
=EPOCHTODATE(A2, 2)

The forward formulas above all give UTC, so a reader in another time zone sees the UTC clock time unless you shift it. For a reader in New York in June, the offset is UTC-4. Put its hours in cell B2 as a signed number, and the formula adds that many hours as a fraction of a day:

=A2/86400+DATE(1970,1,1)+B2/24

New York is UTC-4 in June and UTC-5 in January, so a B2 set for June shows the wrong time in January. A fixed offset only works when every date in the sheet shares it. For anything that crosses a daylight saving change, look up the offset for each date in a table, or convert the times before they reach the sheet.