How to Use the EPOCHTODATE and DATEVALUE Functions in Google Sheets to Convert Timestamps into Readable Dates

Data exports from web servers, API responses, payment gateways, and database logs frequently format timestamps as Unix epoch values—a long integer representing the total number of seconds or milliseconds elapsed since January 1, 1970 (UTC). Historically, converting these integers into human-readable calendar dates in Google Sheets required tedious division formulas based on the number of seconds in a day. With native functions like EPOCHTODATE and DATEVALUE, date parsing in Google Sheets is simpler and more reliable than ever.

Converting Unix Epoch Timestamps with EPOCHTODATE

The EPOCHTODATE function natively decodes Unix timestamps without requiring manual arithmetic:

• Syntax: =EPOCHTODATE(timestamp, [unit])
• Units Parameter: Supply 1 for timestamps measured in seconds (standard 10-digit Unix timestamps), or 2 for timestamps measured in milliseconds (standard 13-digit JavaScript timestamps, which is the default if omitted).

Step-by-Step Timestamp Conversion Examples

Follow these practical conversion formulas based on your dataset formatting:

1. Standard 10-Digit Unix Timestamp: If cell A2 contains 1700000000 (seconds):
   =EPOCHTODATE(A2, 1)
   Google Sheets instantly converts the number to a standard serial date number corresponding to November 14, 2023.
2. Standard 13-Digit Millisecond Timestamp: If cell A2 contains 1700000000000 (milliseconds):
   =EPOCHTODATE(A2, 2)
3. Formatting the Output Cell: Once the formula is evaluated, navigate to Format > Number > Date time in the top menu bar to display the output with your preferred calendar layout and time zone.

Converting Text Strings into Serial Dates with DATEVALUE

When imported data represents dates as plain unformatted text (for example, "2024-10-15" or "15/10/2024"), mathematical sorting and calendar calculations fail because Google Sheets treats the cells as string literals. The DATEVALUE function resolves this:

• Converting Text to Serial Date:
  =DATEVALUE(B2)
• Combining with ArrayFormula: Convert an entire column of text dates at once:
  =ARRAYFORMULA(IF(B2:B<>"", DATEVALUE(B2:B), ""))

Using EPOCHTODATE and DATEVALUE removes brittle manual math from your workflows, ensuring consistent date normalization across all external data feeds in Google Sheets.